Consulting

Results 1 to 19 of 19

Thread: Auto filter in all worksheets in VBA.

  1. #1
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location

    Post 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?


    Thanks for your help.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    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
    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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
    Capture.JPG
    Good morning,
    it doesn't work, see image below.


    Thanks
    Attached Images Attached Images
    • File Type: jpg 1.jpg (59.3 KB, 3 views)

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,476
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location
    Good morning p45cal,
    does not work,


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


    Thanks and best regards
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,476
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location
    Not even all 2, I am attaching the 2 error images

    1.jpg


    and the other

    2.jpg

    Thank You

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,476
    Again:
    Quote Originally Posted by p45cal View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    Quote Originally Posted by Yegimor View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location
    So what can I do to attach a 3.2mb zipped file which always tells me failed

  11. #11
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,916
    Location
    Try uploading a smaller sample of your file
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    Quote Originally Posted by Yegimor View Post
    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
    Last edited by Paul_Hossler; 11-29-2021 at 04:01 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location
    Goodmorning everyone

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


    Thanks and best regards
    Attached Files Attached Files

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,344
    Location
    Quote Originally Posted by p45cal View Post
    Again:
    Or write some code that actually makes some sense!
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,476
    Quote Originally Posted by Bob Phillips View Post
    Or write some code that actually makes some sense!
    It looks like that comment is directed at me. Is it?

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,344
    Location
    Quote Originally Posted by p45cal View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  17. #17
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,476

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,098
    Location
    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?
    1. Original answer: Yes


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


    Capture.JPG


    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/show....php?69400-VBA
    Last edited by Paul_Hossler; 11-30-2021 at 10:56 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    VBAX Regular
    Joined
    Nov 2021
    Posts
    14
    Location
    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
    Last edited by Aussiebear; 12-01-2021 at 04:38 AM. Reason: Added code tags to supplied code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •