Results 1 to 5 of 5

Thread: VBA Run macros on multiple sheets, not all sheets

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post VBA Run macros on multiple sheets, not all sheets

    Hello, I have a button running three macros. I was to have this run through multiple sheets in a workbook, but not all the sheets. Just selected sheets. I have this code on the button. It seems to run through and select the sheets as the message box pops up. But it is not actually running the macros on the non-active sheets. (please excuse me if I am not clear, this is my first post to this forum). Any suggestions would be so appreciated. Thank you, Brooke

    Sub Button1_Click()
    Dim WkSheets As Variant, SheetName As Variant, ws As Worksheet

    '** SET The Sheet Names - MUST Reflect Each Sheet Name Exactly!
    WkSheets = Array("ATC1", "BEC1", "DKC1")

    For Each SheetName In WkSheets

    MsgBox SheetName

    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = SheetName Then

    Call CheckGoalSeekRev 'Macro1
    Call CheckGoalSeekCos 'Macro2
    Call gsMultiCols 'Macro3


    End If
    Next
    Next
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,890
    Post Thanks / Like
    Mentioned
    403 Post(s)
    Tagged
    42 Thread(s)

    Default Re: VBA Run macros on multiple sheets, not all sheets

    Hi & welcome to MrExcel.
    Try
    Code:
    Sub Button1_Click()
       Dim ws As Worksheet
       
       '** SET The Sheet Names - MUST Reflect Each Sheet Name Exactly!
       WkSheets = Array("ATC1", "BEC1", "DKC1")
       
       For Each ws In Sheets(Array("ATC1", "BEC1", "DKC1"))
          ws.Select
          Call CheckGoalSeekRev 'Macro1
          Call CheckGoalSeekCos 'Macro2
          Call gsMultiCols 'Macro3
       Next ws
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA Run macros on multiple sheets, not all sheets

    Welcome to the Board!

    That is because looping through the sheets does not actually select/activate them.
    Add the following line before your first Macro Call:
    Code:
    ws.Activate
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run macros on multiple sheets, not all sheets

    Thank you! This works perfectly!

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA Run macros on multiple sheets, not all sheets

    You are welcome.
    Glad we were able to help.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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