Reapply Filter to Multiple Worksheets

Macgyver01

New Member
Joined
Feb 9, 2010
Messages
2
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!!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
This works for me

Code:
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

New Member
Joined
Feb 9, 2010
Messages
2
I really appreciate the reply, but unless I did somethign wrong, that code only took away the filter on my worksheet. Any ideas?
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
Run it once to remove filters and again to re-apply the filters, or vice-versa
 

rustynissan

New Member
Joined
May 3, 2012
Messages
1

ADVERTISEMENT

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:

Code:
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. :unsure:
 

Imanat

New Member
Joined
Jul 27, 2017
Messages
2
This works for me

Code:
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

New Member
Joined
Jul 27, 2017
Messages
1

ADVERTISEMENT

This works for me

Code:
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.

Code:
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

New Member
Joined
Jul 19, 2014
Messages
11
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

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
You have obviously tweaked the code to reference cell A1, can you post the code you have tweaked?
 

HawaiianShirts

New Member
Joined
Jul 19, 2014
Messages
11
You have obviously tweaked the code to reference cell A1, can you post the code you have tweaked?

Sure! Here it is.

Code:
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
 

Forum statistics

Threads
1,136,780
Messages
5,677,688
Members
419,712
Latest member
LearningCR

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