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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,871
Messages
5,834,150
Members
430,261
Latest member
quangtinator

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