Is there a way that the autofilter selected in worksheet one can be
automatically applied to worksheet two (and vice versa)
This is an adaptation of some code posted by Debra Dalgleish
Change "Sheet2" to the name of the second sheet.
Private Sub Worksheet_Deactivate()
Dim i As Integer
Dim filt As Filter
Dim Op As Long
Dim rng As Range
Dim rng1 As Range
If Me.FilterMode = False Then
Exit Sub
End If
With Worksheets("Sheet2")
Set rng = Me.AutoFilter.Range
If .AutoFilterMode = False Then
.Range(rng.Address).AutoFilter
End If
If .FilterMode Then _
.ShowAllData
Set rng1 = .Range(rng.Address)
End With
i = 0
For Each filt In Me.AutoFilter.Filters
i = i + 1
If filt.On Then
On Error Resume Next
Op = filt.Operator
On Error GoTo 0
If Op = 0 Then
rng1.AutoFilter Field:=i, _
Criteria1:=filt.Criteria1
Else
rng1.AutoFilter Field:=i, _
Criteria1:=filt.Criteria1, Operator:=Op, _
Criteria2:=filt.Criteria2
End If
End If
Next
End Sub
--
Regards,
Tom Ogilvy
"David Mitchell" <david.a....@talk21.com> wrote in message
news:c3b0fc6f.03111...@posting.google.com...
Put code in both sheets and works perfectly.
Kind regards
David A Mitchell