Invoking autofilter 'automatically'

K

Kierano

I have two datasheets, one with three different headings, e.g. A

B

C

The other datasheet has a number of different entries, but with one of the
headings next to them, e.g. Egg A
Apple A
Custard B
Milk C


If I select 'A' on the first datasheet, I want the second one to display
only the values relevant entries to 'A'.

Autofilter will obviously do a great job of splitting my entries into 3
types, but how can I invoke autofilter automatically based on the heading I
choose from the first datasheet?

Any advice gratefully received.
 
S

Stefi

One solution to run this macro:

Sub test()
Worksheets("Sheet1").Select
currentcell = ActiveCell.Address(False, False)
Worksheets("Sheet2").Select
Selection.AutoFilter Field:=2,
Criteria1:=Worksheets("Sheet1").Range(currentcell), Operator:=xlAnd
End Sub

Regards,
Stefi


„Kierano†ezt írta:
 
K

Kierano

Thanks, but I'm afraid this didn't work, since complile error:syntax error is
occurring.
 
I

ice

Try this one...

Range("A1").Select
Sheets("Sheet1").Select
Selection.AutoFilter Field:=2, Criteria1:="a"
ActiveWindow.SmallScroll Down:=-9
End Sub
 
K

Kierano

Great. Works fine!

Have used forms to assign macros to buttons and edited to appropriate names.

Thanks and have a good day.

Only
 
I

ice

Glad I helped!!
Just rate the post in order to stop others from ansewring too.

have a nice day!!!:)
 
Top