Running multiple macros across multiple tabs simultaneously

[TL05]

I have multiple macros within a workbook that I am looking to combine so they can run simultaneously. This is an example of an individual macro which places outputs into a tab named "Game 1":

Sub Game1()

Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

Application.Calculation = xlCalculationManual
Dim ITERATIONS
ITERATIONS = ThisWorkbook.Sheets("INPUT").Cells(14, 3)

For j = 5 To ITERATIONS

Application.Calculate

With Sheets("Game 1")
.Cells(j, 1) = Sheets("INPUT").Cells(2, 23)
.Cells(j, 2) = Sheets("INPUT").Cells(3, 23)
.Cells(j, 3) = Sheets("INPUT").Cells(4, 23)
.Cells(j, 4) = Sheets("INPUT").Cells(5, 23)
.Cells(j, 5) = Sheets("INPUT").Cells(6, 23)

End With

Next j

Application.Calculation = xlCalculationAutomatic

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

I have the same code for Games 2 - 9 which send outputs to their respective tabs when ran individually.

I am looking to combine into one macro that spits outputs onto 9 different tabs simultaneously.

Any help would be greatly appreciated.

Thanks!

 

[DanteAmor]

Hi and welcomet to MrExcel!

Try this:

Sub Game1_9()
  Dim StartTime As Double
  Dim SecondsElapsed As Double
  Dim ITERATIONS
  Dim i As Long, j As Long
  
  StartTime = Timer
  Application.Calculation = xlCalculationManual
  ITERATIONS = ThisWorkbook.Sheets("INPUT").Cells(14, 3)
  
  For i = 1 To 9
    For j = 5 To ITERATIONS
      Application.Calculate
      With Sheets("Game " & i)
        .Cells(j, 1) = Sheets("INPUT").Cells(2, 23)
        .Cells(j, 2) = Sheets("INPUT").Cells(3, 23)
        .Cells(j, 3) = Sheets("INPUT").Cells(4, 23)
        .Cells(j, 4) = Sheets("INPUT").Cells(5, 23)
        .Cells(j, 5) = Sheets("INPUT").Cells(6, 23)
      End With
    Next j
  Next i
  
  Application.Calculation = xlCalculationAutomatic
  SecondsElapsed = Round(Timer - StartTime, 2)
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

 

[bebo021999]

In general case: Game names could be any textstring, not from 1-9 only.
Add these lines into:

Dim GameList, i&
GameList = array("Game A,"Game B","Game C","Game XYZ","Game 5","Game 6","Game 7","Game 8","Game 9")
For i =0 to ubound(GameList)
With Sheets(GameList(i))
For j = 5 To ITERATIONS
Application.Calculate
.Cells(j, 1) = Sheets("INPUT").Cells(2, 23)
.Cells(j, 2) = Sheets("INPUT").Cells(3, 23)
.Cells(j, 3) = Sheets("INPUT").Cells(4, 23)
.Cells(j, 4) = Sheets("INPUT").Cells(5, 23)
.Cells(j, 5) = Sheets("INPUT").Cells(6, 23)
Next
End With
Next

 

[TL05]

Hi, Thanks for your reply!

Unfortunately, that solution is only generating outputs into Game 1 tab.

I know I mentioned I had the same code for each game but they are actually different as they reference different cells. For example:

(as above) Game 1:

Sub Game1()

Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

Application.Calculation = xlCalculationManual
Dim ITERATIONS
ITERATIONS = ThisWorkbook.Sheets("INPUT").Cells(14, 3)

For j = 5 To ITERATIONS

Application.Calculate

With Sheets("Game 1")
.Cells(j, 1) = Sheets("INPUT").Cells(2, 23)
.Cells(j, 2) = Sheets("INPUT").Cells(3, 23)
.Cells(j, 3) = Sheets("INPUT").Cells(4, 23)
.Cells(j, 4) = Sheets("INPUT").Cells(5, 23)
.Cells(j, 5) = Sheets("INPUT").Cells(6, 23)

End With

Next j

Application.Calculation = xlCalculationAutomatic

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub
... Game 2 through to 9 follow the same template but reference different cells. For example, Game 2:

Sub Game2()

Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

Application.Calculation = xlCalculationManual
Dim ITERATIONS
ITERATIONS = ThisWorkbook.Sheets("INPUT").Cells(14, 3)

For j = 5 To ITERATIONS

Application.Calculate
With Sheets("Game 2")
.Cells(j, 1) = Sheets("INPUT").Cells(58, 23)
.Cells(j, 2) = Sheets("INPUT").Cells(59, 23)
.Cells(j, 3) = Sheets("INPUT").Cells(60, 23)
.Cells(j, 4) = Sheets("INPUT").Cells(61, 23)
.Cells(j, 5) = Sheets("INPUT").Cells(62, 23)

End With

Next j

Application.Calculation = xlCalculationAutomatic

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

Is there a way to combine the macros for all the Games into 1 single macro, baring in mind each game references different rows?

Thanks!

 

[DanteAmor]

Unfortunately, that solution is only generating outputs into Game 1 tab.

Of course my code works on all sheets. Only that you omitted to mention the differences between one sheet and another.

In the macro you must update the names of the sheets and the numbers of rows, I put some examples, but you must make the adjustments.
Try this:

Sub Game1_9()
  Dim i As Long, j As Long
  Dim arr As Variant
  
  Application.Calculation = xlCalculationManual
  
  'Indicates the name of the sheet and then the initial row
  arr = Array("Game 1", 2, "Game 2", 58, "Game 3", 4, "Game 4", 60, "Game 5", 6, _
              "Game 6", 62, "Game 7", 8, "Game 8", 64, "Game 9", 10)
  For i = 0 To UBound(arr) Step 2
    For j = 5 To ThisWorkbook.Sheets("INPUT").Cells(14, 3).Value
      Application.Calculate
      Sheets(arr(i)).Range("A" & j).Resize(1, 5).Value = Application.Transpose(Sheets("INPUT").Cells(arr(i + 1), 23).Resize(5, 1).Value)
    Next j
  Next i
  
  Application.Calculation = xlCalculationAutomatic
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

----

 

[TL05]

Thanks Dante - that works great! Appreciate the help.

If I were to also want to extract data from column 40 on the input sheet & add it to the Game tabs, how would you go about that?

This is the updated code below, looking to add a reference to both columns 23 & 40 from the input sheet.

Sub Game1_9()
Dim i As Long, j As Long
Dim arr As Variant

StartTime = Timer
Application.Calculation = xlCalculationManual

'Indicates the name of the sheet and then the initial row
arr = Array("Game 1", 2, "Game 2", 58, "Game 3", 114, "Game 4", 170, "Game 5", 226, _
"Game 6", 282, "Game 7", 338, "Game 8", 394, "Game 9", 450)
For i = 0 To UBound(arr) Step 2
For j = 5 To ThisWorkbook.Sheets("INPUT").Cells(14, 3).Value
Application.Calculate
Sheets(arr(i)).Range("A" & j).Resize(1, 54).Value = Application.Transpose(Sheets("INPUT").Cells(arr(i + 1), 23).Resize(54, 1).Value)
Next j
Next i

Application.Calculation = xlCalculationAutomatic
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

Thanks!

 

[DanteAmor]

Do not forget the recommendations:

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.


Maybe:

Sub Game1_9()
  Dim i As Long, j As Long
  Dim arr As Variant
  
  Application.Calculation = xlCalculationManual
  
  'Indicates the name of the sheet and then the initial row
  arr = Array("Game 1", 2, "Game 2", 58, "Game 3", 4, "Game 4", 60, "Game 5", 6, _
              "Game 6", 62, "Game 7", 8, "Game 8", 64, "Game 9", 10)
  For i = 0 To UBound(arr) Step 2
    For j = 5 To ThisWorkbook.Sheets("INPUT").Cells(14, 3).Value
      Application.Calculate
      Sheets(arr(i)).Range("A" & j).Resize(1, 54).Value = Application.Transpose(Sheets("INPUT").Cells(arr(i + 1), 23).Resize(54, 1).Value)
      Sheets(arr(i)).Range("BD" & j).Resize(1, 54).Value = Application.Transpose(Sheets("INPUT").Cells(arr(i + 1), 40).Resize(54, 1).Value)
    Next j
  Next i
  
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[TL05]

Brilliant, that's great.

Thanks DanteAmor