Excel autofilter selection criteria referencing the contnet of a cell on another work

[Rabbitoh]

I need to have the autofilter in Excel reference the content of a cell on a different worksheet (but in same workbook) and use that content as the filtering criteria for the column being filtered.

Example:

Column 1 in the Table worksheet contains the word "Nelson"
Instead of just filtering the word "Nelson" as per normal, I need the code to direct the filter to go to the worksheet "Setup", locate the field B5, then use the entry in the cell as the filter criteria for column 1 back on the Table worksheet.

This will then allow me to change the entry in the cell on the Setup worksheet at any time yet still have the code do it's stuff.

I know the follwing code doesn't work but it is just a sample.

Selection.AutoFilter Field:=1, Criteria1:=("setup!b5"), Operator:=xlAnd

any ideas ?

 

[Rabbitoh]

I have two solutions for this, the first from another source and the second I worked out myself:

Selection.AutoFilter _
Field:=1, _
Criteria1:=ThisWorkbook.Worksheets("Setup").Range("B5").Value, _
Operator:=xlAnd

and:

TW = Worksheets("Lookup").Range("B3")
If TW = "" Then Exit Sub
Selection.AutoFilter Field:=5, Criteria1:=TW