Apply filter to all sheets

[auroralily]

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

 

[ParamRay]

.
.

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

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]

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:

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