hmm..Thanks Bernie for replying - appreciate it. Perhaps my explanation
was not good enough - what im trying to do is to put the active cell
reference in the Autofilter field column, (not the criteria column) ie I
dont know which field (1,2,3 or 4) to run the criteria (Y/N) on...

(within master sheet I have 4 "type" columns - Type - Cat is 1st column,
Type mouse is second col etc etc. Within the column I have a Y or a N.
The reason for 4 columns and not 1 is that you could have an entry that
releates to 1,2,3 or 4 columns (perhaps imagine owners as details on the
rows)
Thus when a user clicks elsewhere, I want excel to "choose" which column
to apply the autofilter to.
Perhaps named ranges would work...?

Kind Regards

Darin




Darin,

Of course, this depends on the location and layout of your datatable on
sheet Master (specifically,
which column has the Tyupe of Animal field):

With Worksheets("Master").Range("A1").CurrentRegion
.AutoFilter Field:=2, Criteria1:=ActiveCell.Value
.AutoFilter Field:=13, Criteria1:="Y"
End With


--
HTH,
Bernie
MS Excel MVP



*** Sent via Developersdex http://www.developersdex.com ***