VBA Autofilter Question

[Cellardoor]

Dear all

I have this code to autofilter my list object

Range("Table1[[#Headers],[Unit]]").Select
Selection.AutoFilter
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, _
Criteria1:="Company1"

But i will need to reply this code many times so i would ask you if its possible to Define this criteria only in the beginning of the Macro. If so, i would only need to change the criteria one time.

Something like this:

Set Criteria as Criteria 1
Criteria = Company1


In my code i would only have:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, _
Criteria1:="Criteria"

And by this i would only need to change the Company1 to Company2 or Company3 one time.

Is it clear?

Many Thanks

 

[Luke M]

Somethine like this then:

Sub Example()
Dim myCrit As String
myCrit = "Company 1"
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=myCrit
End Sub

Or, if instead of having to modify the code everytime, you could have the code ask user:

Sub Example2()
Dim myCrit As String
myCrit = InputBox("What is the criteria?", "Criteria")
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=myCrit
End Sub

 

[Cellardoor]

Hey Hey...Many thanks for the help

Tomorrow i'll try it at work...But its fine for sure.

Also thanks to Bobhc for the redirection of the post