Macro VBA

yegimor216 · Nov 28th 2021

Good evening everyone,

I have 4 worksheets:

Sheet1

Sheet2

Sheet3

Sheet4

I would like to know if there is a VBA Macro at the time of opening does the "Reapply All" filter (Filter) of all worksheets work?

Thanks for your help.

Fuzz-Head · Nov 28th 2021

Your question is pretty open with assumptions. But You can use this to get you started. Copy this to your Personal Macro and work this till you get what you want then you can move it to a WorkBook Open event.

This code assumes that you always want to apply filters to the 1st row of all the worksheets in your book.

Code

Sub FilterHeaders()

Dim ws As Worksheet

For Each ws In Worksheets
    ws.Range("a1").AutoFilter
Next ws

End Sub

yegimor216 · Nov 28th 2021

Good evening Fuzz-Head,

Thanks for the answer, but I have a doubt but the code, where should I put?

Should I put "This Workbook" or VBA of Sheet1-2-3-4?

Have a good evening

**royUK** · Nov 29th 2021

The code needs to be placed in the WorkBook Open event - Where to put the code

Code

Private Sub Workbook_Open()
    Dim oWs As Worksheet
    
    For Each oWs In Worksheets ''///check for AutoFilter, if not in use apply
        If Not oWs.AutoFilterMode Then oWs.Range("A1").AutoFilter
    Next oWs
End Sub

You could Use Tables to make it easier to identify the data.

Overview of Excel Tables.

This code uses Tables and you do not need to know the address of the Table

Code

Private Sub Workbook_Open()
    Dim oWs As Worksheet
    
    For Each oWs In Worksheets ''///check for AutoFilter, if not in use apply
        If Not oWs.AutoFilterMode Then oWs.ListObjects(1).Range.AutoFilter
    Next oWs
End Sub

yegimor216 · Nov 29th 2021

Good morning royUK,

does not work,

I am attaching the file, so you can see if I was wrong.

Scadenziario Generale1.zip

Thanks and best regards

**royUK** · Nov 29th 2021

You have two WorkBook Open events, you can only have one of each event.

You are using the code for Tables but haven't converted your data to Tables.

**royUK** · Nov 29th 2021

Try this, I'm not sure what your Tester codes are for

yegimor216 · Nov 29th 2021

Dear royUK,

Not good, I'll explain the same:

-Sheet "Mese Corrente" use the filter column D from the oldest up to today's date;

-Sheet "Mese successivo" use the filter column D from the oldest up to the date of the following month only;

-Sheet "2 Mesi successivi" use the filter column D from the oldest up to the date only 2 months later;

- "Generale" sheet use the filter column D from the oldest to the most recent;

-Sheet "Da incollare" use the filter column D from the oldest to the most recent.

Thanks

**royUK** · Nov 29th 2021

Quote

I would like to know if there is a VBA Macro at the time of opening does the "Reapply All" filter (Filter) of all worksheets work?

Your last comment is not the same!

Macro VBA

Participate now!

Share

Tags