VBA - setting autofilter options

[Puppies72]

Hi all,

I have a sheet that is autofiltered and want to write into a macro certain combinations of autofilter - how can I do this?

For example - column A:AS is autofiltered and the dropdown for column K shows the numbers 1 through 16 - I want to be able to automatically set that autofilter to the value shown in A1 on a different sheet.

Can someone show me that example and I can work the rest out from it

many thanks in advance

 

[John_w]

Try recording a macro and editing the code to use the criteria value from the other sheet. The result should be something like:

Selection.AutoFilter Field:=11, Criteria1:=Sheets("Other_Sheet").Range("A1").Value

 

[Puppies72]

Great stuff thank you John - already had the macro recorded but didn't know how to make it set the value to that in another sheet!

Ended up with

Sheets("Terrain").Select
    ActiveSheet.Range("$A$3:$AS$10003").AutoFilter Field:=11, Criteria1:=Sheets("Ships").Range("A1").Value
    ActiveSheet.Range("$A$3:$AS$10003").AutoFilter Field:=15, Criteria1:="<>"