Linking Or Copying Filter Arrow

T

TONYC

I have a created a spreasheet with autofilters in a worksheet name
"List", and created macros on a main menu worksheet named "Search".
am wanting to either link or copy the dropdown arrows in the filte
mode within the "List" worksheet, on to the "Search" worksheet, lisitn
the selection criteria.

Is this possible
 
T

Tom Ogilvy

the autofilter object has a filters collection which can be queried to get
the filter criteria.

each filter also has an ON attribute which if true indicates it is being
applied.

Here is some code for getting information from the second column in the
Autofilter: (as an example)

Sub ShowCriteria()
Dim Afil As AutoFilter
Set Afil = ActiveSheet.AutoFilter
Dim sOp As String, op As Long
Dim sStr1 As String, sStr2 As String
On Error Resume Next
sStr1 = Afil.Filters(2).Criteria1
op = Afil.Filters(2).Operator
sStr2 = Afil.Filters(2).Criteria2
Select Case op
Case xlAnd: sOp = "And"
Case xlBottom10Items: sOp = "Bottom 10"
Case xlBottom10Percent: sOp = "Bottom 10%"
Case xlOr: sOp = "Or"
Case xlTop10Items: sOp = "Top 10"
Case xlTop10Percent: sOp = "Top 10%"
Case Else: sOp = "Nothing"
End Select
On Error GoTo 0
Debug.Print sStr1 & " " & sOp & "" & sStr2
End Sub
 
Top