Add filters on selected sheets at once

1

Dear Sir

I have the following macro to add filters on all worksheets based on Variatus  suggestion in May 28 , 2018. It works perfecly as  I wanted before.

Refer to the link 

https://www.teachexcel.com/talk/2740/add-filters-on-all-worksheets#topic_answer_section_anchor

I want to have the modified macros that look at several sheets at once, and  run the code on the Activate event just for the sheet i am really looking at.

Best regards

Arsil Hadjar

Sub AddFilter_on_allWorksheet()
   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))
            Rng.AutoFilter
            .Activate
            .Range("J4").Select
        End With
        With ActiveWindow
            If .FreezePanes Then .FreezePanes = False
            .FreezePanes = True
        End With
    Next Sh
    Application.ScreenUpdating = True
End Sub
 
Answer
Discuss

Answers

0
Selected Answer

You need to change the above macro and call it from the worksheet where you want it to run.

Here is the above macro:

Sub AddFilter_on_Current_Worksheet()
    Dim Sh As Worksheet
    Dim Rng As Range

    Application.ScreenUpdating = False          ' stop screen flicker

    Set Sh = ActiveSheet

    With Sh
        If .AutoFilterMode Then .AutoFilterMode = False
        Set Rng = Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))
        Rng.AutoFilter
        .Activate
        .Range("J4").Select
    End With
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .FreezePanes = True
    End With

    Application.ScreenUpdating = True
End Sub

To run on specific worksheets double-click the worksheets where you want it in the VBA window and paste this:

Private Sub Worksheet_Activate()

Call AddFilter_on_Current_Worksheet

End Sub

To have it run for ALL worksheets, go to the VBA window and double-click ThisWorkbook and paste this code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Call AddFilter_on_Current_Worksheet

End Sub
Discuss

Discussion

Dear Don,

Many thanks for the suggestion and modified Macro.
It works perfectly

Best regards
Arsil Hadjar
Arsil (rep: 16) Aug 10, '18 at 2:54 am
Add to Discussion

Answer the Question

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