Apply filter to all sheets

auroralily

New Member
Joined
Sep 13, 2014
Messages
24
Hi

I have a workbook showing students grades and targets in the three main sciences. Thanks to a lot of help from this forum I have already completed a macro that automatically plots a graph with 6 series for each students row of data (each series has its data on a different sheet).

At the moment my macro clears the autofilter before plotting and produces charts for the whole student set.

Is it possible to vba code the following?:
  • I apply a filter to the dataset on sheet1
  • A macro applies the same filter to sheets 2 - 6 (ideally done automatically but on button press is fine)

I am using excel 2010.

Thanks
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Try the following event handler procedure, which should be placed in the code module corresponding to Sheet1:

Code:
Private Sub Worksheet_Deactivate()

    Dim Wkst As Worksheet
    
    For Each Wkst In ThisWorkbook.Worksheets
        
        If Wkst.Index >= 2 And Wkst.Index <= 6 Then
            Wkst.AutoFilterMode = False
            
            If Me.AutoFilterMode Then
                Wkst.Range(Me.AutoFilter.Range.Item(1).Address).AutoFilter
            End If
        
        End If
    
    Next Wkst

End Sub
 

auroralily

New Member
Joined
Sep 13, 2014
Messages
24
Hi,

Thanks for your reply. I tried the code as you suggested it but had to tweak it as the index numbers on my sheet had gotten a little messed up.

The code reset the filters on the first sheet instead of copying the filter. I changed it slightly to work on a button press but the filters are not being copied.

This is the code I have at the moment:

Code:
Sub Apply_Filters()

    Dim b1 As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Set b1 = Workbooks("Progress Tracker GCSE Triple")
    Set ws1 = b1.Worksheets("BiologyData")
    
    For Each ws In b1.Worksheets
        
        If InStr(ws.Name, "Data") > 0 And InStr(ws.Name, "BiologyData") = 0 Then
            ws.AutoFilterMode = False
            
            If ws1.AutoFilterMode Then
                ws.Range(ws1.AutoFilter.Range.Item(1).Address).AutoFilter
            End If
        
        End If
    
    Next ws
End Sub

Can you see anything that would explain this problem.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,122,686
Messages
5,597,536
Members
414,153
Latest member
SandraC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top