VBA Run macros on multiple sheets, not all sheets

brookementer

New Member
Joined
Jun 3, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,135
Office Version
365
Platform
Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,470
Office Version
365
Platform
Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,470
Office Version
365
Platform
Windows
You are welcome.
Glad we were able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,862
Messages
5,471,177
Members
406,747
Latest member
smstrickland

This Week's Hot Topics

Top