Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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
 
excel
MS Office Version: 2010
posted by: Arsil (rep: 32) on Thu Aug 9, 2018 at 9:58 pm
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
posted by: don (rep: 1989) on Fri Aug 10, 2018 at 2:31 am
Discuss

Discussion

Dear Don,

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

Best regards
Arsil Hadjar
Arsil (rep: 32) 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