Autofilter More than One Worksheet

[David Mitchell]

I have a spreadsheet which has two worksheets, the first represents 6
months Jan to June and the second July to December. Each worksheet
has the same data in the first three columns (first name, surname,
teamname) I have autofilter set up to allow the user to filter either
their own record (by surname) or by their team. Unfortunately they
have to select this in each worksheet

Is there a way that the autofilter selected in worksheet one can be
automatically applied to worksheet two (and vice versa)

 

[Tom Ogilvy]

Right click on the sheet tab of the first sheet and select view code. Put
in this event.

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

 

[David Mitchell]

Many thanks, Tom (and Debra Dalgleish)

Put code in both sheets and works perfectly.

Kind regards

David A Mitchell