Reapply Filter to Multiple Worksheets

[Macgyver01]

I have an Excel file with multiple worksheets that each have an autofilter applied to the same set of data. The filtered cells are pulling from data on another worksheet that I need to update regularly and have the worksheets with filters recalculate or reapply accordingly. Right now the quickest way I have found to update all these filters is to either use the "reapply" button on the filter menu, or the shortcut of "Ctrl, Alt, L" after individually selecting each worksheet. CAN SOMEONE PLEASE HELP ME CREATE A SHORTCUT TO REAPPLY THE FILTER TO ALL WORKSHEETS AT THE SAME TIME?? Thanks in advance!!!

 

[pboltonchina]

This works for me

Sub autofilter()
 Application.ScreenUpdating = False
 For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.UsedRange.Select
                Selection.autofilter
                Next ws
 Application.ScreenUpdating = True
 
End Sub

 

[Macgyver01]

I really appreciate the reply, but unless I did somethign wrong, that code only took away the filter on my worksheet. Any ideas?

 

[pboltonchina]

Run it once to remove filters and again to re-apply the filters, or vice-versa

 

[rustynissan]

I have found this works pretty great with Tables. Right click on the Sheet name tab and select "View Code", then add this in there:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=3, Criteria1:="=True"
End Sub

This filters the table rows by the values in Column 3 of the table that contain the value "TRUE". Some tweaking to this and you can apply it to multiple sheets/tables. This will update automatically. I can't believe Excel doesn't have this functionality built in.

 

[Imanat]

This works for me

Sub autofilter()
 Application.ScreenUpdating = False
 For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.UsedRange.Select
                Selection.autofilter
                Next ws
 Application.ScreenUpdating = True
 
End Sub

So where do I paste it if I hae 20 sheets pulling data from cells of Sheet 1, will this go for the workbook?

 

[JBredensteiner]

This works for me

Sub autofilter()
 Application.ScreenUpdating = False
 For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.UsedRange.Select
                Selection.autofilter
                Next ws
 Application.ScreenUpdating = True
 
End Sub

I know it's been a long time since this thread was active, but I was trying to do the same thing as the OP, and I believe I found an ok solution... pboltonchina, your post helped me come up with the below code. I have three tables on three worksheets (nine total) that I want to have refreshed (sort and filter). The three tables are in the same spot on all three worksheets.

Sub RefreshMyFilters()
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        On Error Resume Next
            Range("B9").Select
            ActiveSheet.AutoFilter.ApplyFilter
            
            Range("B32").Select
            ActiveSheet.AutoFilter.ApplyFilter
            
            Range("B51").Select
            ActiveSheet.AutoFilter.ApplyFilter
            
            Range("B3").Select
    Next ws
    
    Sheets("Sheet1").Select

    Application.ScreenUpdating = True

End Sub

Hopefully this will help someone else someday

 

[HawaiianShirts]

Hopefully this will help someone else someday

I have a workbook with multiple sheets. One is the "Master" sheet where all updates are made. The others refer to the "Master" sheet with =Index(Match()) formulas, but they are protected against editing (no password, just locked cells). When updates are made to column A in the "Master" sheet, I want the other sheets to update the filter located in cell A1.

I just tried that code, but I couldn't get it to work automatically. It did work when I manually ran the macro, though it navigated me to the last worksheet when it was done. The trouble is that I'm preparing this workbook for other users who are barely comfortable with basic Excel navigation, let alone filters, so getting them to manually run a macro is unlikely.

What I'd like is to have the macro run by itself every time a change is made to "Master" sheet column A. Any suggestions? (I'm new to VBA, so maybe there's a setting I'm missing...)

 

[pboltonchina]

You have obviously tweaked the code to reference cell A1, can you post the code you have tweaked?

 

[HawaiianShirts]

You have obviously tweaked the code to reference cell A1, can you post the code you have tweaked?

Sure! Here it is.

Sub RefreshMyFilters()
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        On Error Resume Next
            Range("A1").Select
            ActiveSheet.AutoFilter.ApplyFilter
            
    Next ws
    
    Sheets("Sheet1").Select

    Application.ScreenUpdating = True

End Sub