Add filters on all worksheets


I have the following macro to add filters on all worksheets.

The problem with this macro is

  • If some sheets already have autofilters, then running the macro will remove autofilters
  • How to make sure that when running the macro ,all worksheets will have the filters even if the sheets already have autofilters before running the macro.

Looking forward to having your advice in this regards

Best regards

Arsil Hadjar

Sub AddFilter_on_allWorksheet()

 Dim sh As Worksheet

  For Each sh In Worksheets
   ' sh.AutoFilterMode = False

    ActiveWindow.FreezePanes = True
  Next sh
End Sub


Selected Answer

Hello Arsil,

The AutoFilter method is a toggle. Every time it is called it will change to the opposite of what it was. To avoid surprises use the AutoFilterMode to determine the current status.

Sub AddFilter_on_allWorksheet()
    ' 28 May 2018
    Dim Sh As Worksheet
    Dim Rng As Range
    Application.ScreenUpdating = False          ' stop screen flicker
    For Each Sh In Worksheets
        With Sh
            If .AutoFilterMode Then .AutoFilterMode = False
            Set Rng = Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))
        End With
        With ActiveWindow
            If .FreezePanes Then .FreezePanes = False
            .FreezePanes = True
        End With
    Next Sh
    Application.ScreenUpdating = True
End Sub

Your combinatilon of setting AutoFilter and FreezePanes is an unhappy one, at least if you want to do it on all the sheets in one go. AutoFilter doesn't require selection of the sheet whereas FreezePanes does. I have added code to suppress ScreenUpdating while flicking through all the sheets (and reduced the number of columns for which the filter is set).

Actually, I think it is the idea to do all the sheets at once which stifles the smiles. That makes sense only if you want to look at several sheets at once, and even then I would advise to run the code on the Activate event just for the sheet you are really looking at. It would be shorter, fully automatic, and customisable for each sheet (if need should arise for that in the future).



I get the feeling that he just wanted a simple run-once macro that he re-runs every once in a while. I think most people's understanding of macros, especially when they ask questions is rather limited, also when it comes to the application and usage of the macro.
don (rep: 1825) May 28, '18 at 9:04 am
Don, it's philosophy, my friend.
This site is called "Teach Excel". Many visitors treat it like "Free Excel Help" and that's what they get. I choose to address myself to those who want to learn. They should find something here that they won't get elsewhere. Those who find learning too hard are not my clientele.
Variatus (rep: 3838) May 28, '18 at 8:00 pm
Dear Variatus,

Many thanks for the solution. It work perfectly
The use of AutoFilterMode to determine the current status solved the problems.

Best regards
Arsil Hadjar
Arsil (rep: 32) May 28, '18 at 8:04 pm
Dear Variatus.

Back to the May 29 ,2018 discussion above, I am interested in your option
1. to run the macro on the Activate event just for the sheet i am looking at (AddFilter_on_selectedWorksheet).

Would it be possible for you to modify the macro for this purposes

Best regards
Arsil Hadjar 

Arsil (rep: 32) Aug 8, '18 at 7:07 am
Arsil, ask a new question for this since this current question has already been marked as solved.
don (rep: 1825) Aug 8, '18 at 8:02 am
Please post this as a new question so that everybody can participate. Just post the code you have and ask fro the amendment you want.
Variatus (rep: 3838) Aug 8, '18 at 7:55 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login