Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter More than One Worksheet
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter More than One Worksheet
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 -- Regards, Tom Ogilvy "David Mitchell" wrote in message om... 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter More than One Worksheet
Many thanks, Tom (and Debra Dalgleish)
Put code in both sheets and works perfectly. Kind regards David A Mitchell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AUTOFILTER IN A PROTECTED WORKSHEET | Excel Discussion (Misc queries) | |||
autofilter into another worksheet | Excel Discussion (Misc queries) | |||
Autofilter Data to separate worksheet | Excel Discussion (Misc queries) | |||
Using Autofilter on a Protected Worksheet | Excel Discussion (Misc queries) | |||
Autofilter - Link to 2nd worksheet? | Excel Worksheet Functions |