Forum Discussion
Help with Macros?
I'm a newbie when it comes to using macros within an Excel spreadsheet - and am looking for a little help. I have used the "generic" Macro recording tool to create a "filtered" view of a table. All of the Filter criteria has been "manually" selected via the filter dialog. The resulting Macro code is as follows:
Sub Macro1()
Sheets("Transaction Log").Select
ActiveSheet.Range("$A$6:$S$271").AutoFilter Field:=6, Criteria1:="Medical"
ActiveSheet.Range("$A$6:$S$271").AutoFilter Field:=9, Criteria1:="2021"
ActiveSheet.Range("$A$6:$S$271").AutoFilter Field:=7, Criteria1:="Patrick"
End Sub
The resulting filter created when the above code is executed is exactly what I want to see. However, now - I would like to pass the criteria parameters (specifically "year" criteria .... seen in the above "hard code" as "2021" in the form of a variable. The variable value will be the content of a field on another worksheet within the same file. The specific location of cell is New Summary!$D$3 .
Can anybody show me how to define a variable in the "macro preamble" and then use that variable value in the corresponding code that performs the Select?
HansVogelaar That's worked perfectly! Thanks you very much!!!! May I ask a followup question?
My "Transaction Log" table is dynamic (i.e., rows are constantly being added as new expense records are added to the table. Meanwhile - the Range statements contained in the Macros are "explicit". I just noticed that when rows are added to the table - the Macros do not update. How do I modify the range definition to accommodate an constantly expanding range?
4 Replies
Try this:
Sub Macro1() Dim MyYear As String MyYear = Worksheets("New Summary").Range("D3").Value With Worksheets("Transaction Log").Range("A6:S271") .AutoFilter Field:=6, Criteria1:="Medical" .AutoFilter Field:=9, Criteria1:=MyYear .AutoFilter Field:=7, Criteria1:="Patrick" End With End SubHansVogelaar That's worked perfectly! Thanks you very much!!!! May I ask a followup question?
My "Transaction Log" table is dynamic (i.e., rows are constantly being added as new expense records are added to the table. Meanwhile - the Range statements contained in the Macros are "explicit". I just noticed that when rows are added to the table - the Macros do not update. How do I modify the range definition to accommodate an constantly expanding range?
As Peter Bartholomew suggests, I'd use either a defined name or a table.
For the former, see:
