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

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 B6, 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

Answer this question

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

gars1
Hi

Thanks this helped me, but how would I amend it so I can filter for values greater than or equal to the referenced cell
Oguz
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
Excel autofilter selection criteria referencing the contnet of a cell on another worksheet

Answer this question