Active Cell to be used as filter...

[Darin Kramer]

Hi guys, think its a tough one...

I need to run a macro that filters data based on the contents of the
active cell.

Eg within my "info" sheet, I have:
a1 = cat
b1 = dog
c1 = Mouse

In another sheet (master) there are columns, one of which is "type of
animal"

If user is on cell a1 in the "info" sheet and activates the macro, I
need the Macro to filter the column "type of animal" in the "master"
sheet. (I also need to apply a second filter to another column in the
master sheet, but that is not dependent on the users choice so I have
been able to do using .AutoFilter Field:=13, Criteria1:="Y")

Appreciate any thoughts,suggestions and solutions for this novice VBA
user!!!

Thanks

Regards

D



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

 

[Bernie Deitrick]

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

 

[Darin Kramer]

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 ***