Auto filter in all worksheets in VBA.

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/im...ione/1f914.png


Thanks for your help.

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

Code:

---

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

---

Attachment 29190

Quote:

---

Originally Posted by Paul_Hossler

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

Code:

---

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

---

Attachment 29190

---

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


Thanks

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.

Good morning p45cal,
does not work,


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


Thanks and best regards

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:

Code:

---

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:

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
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.

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

Attachment 29193


and the other

Attachment 29194

Thank You

Again:

Quote:

---

Originally Posted by p45cal

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.

---

Quote:

---

Originally Posted by Yegimor

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

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

Try uploading a smaller sample of your file

Quote:

---

Originally Posted by Yegimor

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

---

Like I said

Quote:

---

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

Goodmorning everyone

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


Thanks and best regards

Quote:

---

Originally Posted by p45cal

Again:

---

Or write some code that actually makes some sense!

Quote:

---

Originally Posted by Bob Phillips

Or write some code that actually makes some sense!

---

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

Quote:

---

Originally Posted by p45cal

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.

:)

Original request from post #1

Quote:

---

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/im...ione/1f914.png

---

1. Original answer: Yes


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


Attachment 29197


3. Macro recorder for "Reapply" generates this:

Code:

---

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

Code:

---

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/show....php?69400-VBA

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

Code:

---

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

---