AUTOFILTER QUESTION

[Guest]

Hello. I have an autofilter in two different worksheets. I was wondering if
there is a way to read how a user has set the auto filter on a column one
sheet and apply it to a specific column on another sheet.

Also, is there a way to modify the default list for auto filter, or a way to
programatically create a custom autofilter? Thanks a lot for the help.

 

[Tom Ogilvy]

Here is the help example on Autofilter Object. It should give you a good
start on doing what you want to do.

Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String

Sub ChangeFilters()

Set w = Worksheets("Crew")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f, 1) = .Criteria1
If .Operator Then
filterArray(f, 2) = .Operator
filterArray(f, 3) = .Criteria2
End If
End If
End With
Next
End With
End With

w.AutoFilterMode = False
w.Range("A1").AutoFilter field:=1, Criteria1:="S"

End Sub

Also, there is a custom setting in the drop down which allows you to set two
conditions.

You need to turn on the macro recorder and perform some of the actions you
describe and see what is recorded. This will again give you good insight
into what you can do.