2 Autofilters

[N1KO]

Hi,

I have an table which is autofiltered on a worksheet.

I've just had to add another table on another worksheet and i want this one
to filter when i change the filter on the 1st sheet.

Is this possible in VBA?

Thanks in advance

 

[Roger Govier]

Hi

This solution uses a UDF created by Tom Ogilvy to find the criteria that
have been set on a Sheet's Autofilter
I used Sheet2 and Sheet3.
Adjust to suit your needs.

Sub Filtersame()
Dim setfilter As String
setfilter = ShowFilter(Sheets("Sheet2").Range("A1"))

Sheets("Sheet3").Select
Selection.AutoFilter Field:=1, Criteria1:=setfilter

End Sub

'Here is a user defined function that will display the criteria in a cell:
'created by Tom Ogilvy
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function