Auto filter in all worksheets in VBA.

[Yegimor]

Good moorning,
I have 4 worksheets:


Sheet1


Sheet2


Sheet3


Sheet4


I would like to know if there is a VBA macro when opening the file, does the "Reapply All" filter (Filter) in all worksheets work? https://www.ozgrid.com/forum/core/images/smilies/emojione/1f914.png


Thanks for your help.

[Paul_Hossler]

If I'm understand correctly, try putting something like this in the ThisWorkbook code page



Option Explicit


Private Sub Workbook_Open()
Dim ws As Worksheet

On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
ws.AutoFilter.ApplyFilter
Next
On Error GoTo 0


End Sub


29190

[Yegimor]

If I'm understand correctly, try putting something like this in the ThisWorkbook code page



Option Explicit


Private Sub Workbook_Open()
Dim ws As Worksheet

On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
ws.AutoFilter.ApplyFilter
Next
On Error GoTo 0


End Sub


29190

Good morning,
it doesn't work, see image below.


Thanks

[p45cal]

You probably have more than one
Sub Workbook_Open()
macros in your project; delete all but the one you want or change the name(s) of the one(s) you don't want to run on file open.

[Yegimor]

Good morning p45cal,
does not work,


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


Thanks and best regards

[p45cal]

The macro that will work when the workbook is opened is:
Private Sub Workbook_Open()
NOT:
Private Sub Workbook_Open1()
so swap their names around.

Want both to work? Then either:
Private Sub Workbook_Open()
Dim oWs As Worksheet
Call Tester1
Call Tester2
Call Tester3
Call Tester4
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
or:

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
Call Tester1
Call Tester2
Call Tester3
Call Tester4
End Sub


If there isn't a table (listobject) on each worksheet (which there isn't) you might need to cater for that or as a quick fix bring the On Error Resume Next line back in.

[Yegimor]

Not even all 2, I am attaching the 2 error images

29193


and the other

29194

Thank You

[p45cal]

Again:
If there isn't a table (listobject) on each worksheet (which there isn't) you might need to cater for that or as a quick fix bring the On Error Resume Next line back in.

[Paul_Hossler]

Good morning p45cal,
does not work,


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


Thanks and best regards

That is a MS Word document with a link in it to some other file sharing service

It'd be better to attach the actual workbook with enough data to show the problem to your post

My sig has instructions

BTW, indicating that you're using Tables instead of Ranges would have been useful information, and would have been obvious from an attached sample workbook

[Yegimor]

So what can I do to attach a 3.2mb zipped file which always tells me failed

[Aussiebear]

Try uploading a smaller sample of your file

[Paul_Hossler]

So what can I do to attach a 3.2mb zipped file which always tells me failed

Like I said



actual workbook with enough data to show the problem to your post


we don't need all of it, just a number of worksheets with enough data to represent the issues

[Yegimor]

Goodmorning everyone

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


Thanks and best regards

[Bob Phillips]

Again:

Or write some code that actually makes some sense!

[p45cal]

Or write some code that actually makes some sense!

It looks like that comment is directed at me. Is it?

[Bob Phillips]

It looks like that comment is directed at me. Is it?

No, at the OP. It may not have looked so if my reply with quote hadn't cut what you quoted.

he OP has code the references listobjects, but there are no listobjects on his worksheets.

[p45cal]

:)

[Paul_Hossler]

Original request from post #1

I would like to know if there is a VBA macro when opening the file, does the "Reapply All" filter (Filter) in all worksheets work? https://www.ozgrid.com/forum/core/images/smilies/emojione/1f914.png

1. Original answer: Yes


2. HOWEVER, your file Provo.xlsm wants to use ListObjects, and there aren't any


29197


3. Macro recorder for "Reapply" generates this:



Sub Macro2() Sheets("2 Mesi Successivi").AutoFilter.ApplyFilter
End Sub


Is this what you really want?



4. Based on your workbook, and the question in #1, there's this


Option Explicit


Private Sub Workbook_Open()
Dim ws As Worksheet

On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
If Not ws.AutoFilterMode Then ws.Rows(1).AutoFilter
ws.AutoFilter.ApplyFilter
Next
On Error GoTo 0
End Sub


5. But I think you need to be very specific about what you want to do


EDIT:

6. Is this different from your other post, the files seem to be the same

http://www.vbaexpress.com/forum/showthread.php?69400-VBA

[Yegimor]

Good Morning!
I managed.Thank you and it's a good job!


Option Explicit

Private Sub Workbook_Open ()
Call Tester
Call Tester 1
Call Tester2
Call Tester 3
Dim ws As Worksheet
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
If Not ws.AutoFilterMode Then ws.Rows (1) .AutoFilter
ws.AutoFilter.ApplyFilter
Next
On Error GoTo 0
End Sub