Macros in different sheets

[Erantes]

Hi I am fairly new to excel with a problem I cant solve so help would be great. I have 10 sheets with a macro on each sheet that I want to trigger at the same time. I have looked at loads of YouTube videos but whatever I do it only seems to trigger on the sheet I'm on and none of the other sheets.

 

[kevin9999]

Try the following on a copy of your workbook. You must change the names of your sheets to their actual names.

Option Explicit
Sub Erantes()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("Sheet1")  '<--- *** You need to change the names of the 3 sheets to their actual names ***
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    
    With ws1
        .Range("K9") = "1000"
        .Range("K9:K48").Copy
        .Range("K53").PasteSpecial xlPasteValues
        .Range("C2").Copy
        .Range("C53").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = 0
    
    With ws2
        .Range("K9") = "6.4"
        .Range("K9:K48").Copy
        .Range("K53").PasteSpecial xlPasteValues
        .Range("C2").Copy
        .Range("C53").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = 0
    
    With ws3
        .Range("K9") = "2"
        .Range("K9:K48").Copy
        .Range("K53").PasteSpecial xlPasteValues
        .Range("C2").Copy
        .Range("C53").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True
End Sub

 

[Skyybot]

How do you want to "trigger" them? Do they all do the same thing, just on a different Sheet?

 

[Erantes]

How do you want to "trigger" them? Do they all do the same thing, just on a different

How do you want to "trigger" them? Do they all do the same thing, just on a different Sheet?

Hi yes they do a similar thing on different sheet although it's different data they are recording.

 

[kevin9999]

Hi yes they do a similar thing on different sheet although it's different data they are recording.

You haven't answered @Skyybot 's question as to how you want to Trigger the code?

 

[Erantes]

You haven't answered @Skyybot 's question as to how you want to Trigger the code?

Hi so each macro is to collect information at a certain time on each sheet say 8am. I would like to run one macro that would activate the 10 macros at roughly the same time.

 

[Skyybot]

Just call the other Macros from the one you run. Example:

Sub Sheet1Macro() 'This is in Sheet1
'Do stuff
End Sub

Sub Sheet2Macro() 'This is in Sheet2
'Do stuff
End Sub

Sub YourMainMacro() 'Put this in ThisWorkbook
Sheet1.Sheet1Macro
Sheet2.Sheet2Macro
End Sub

 

[kevin9999]

Can you post the macro you currently have on "each sheet"? Only once if they're the same.

 

[Erantes]

Can you post the macro you currently have on "each sheet"? Only once if they're the same.

 

[Erantes]

Sub RACE1TIME0730()
'
' RACE1TIME0730 Macro
'

'
Range("K9").Select
ActiveCell.FormulaR1C1 = "1000"
Range("K9:K48").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=28
Range("K53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-28
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=39
Range("C53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub RACE2TIME0730()
'
' RACE2TIME0730 Macro
'

'
Range("K9").Select
ActiveCell.FormulaR1C1 = "6.4"
Range("K9").Select
ActiveCell.FormulaR1C1 = "6.4"
Range("K9:K48").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=19
Range("K53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-19
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=34
Range("C53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub RACE3TIME0730()
'
' RACE3TIME0730 Macro
'

'
Range("K9").Select
ActiveCell.FormulaR1C1 = "2"
Range("K9").Select
ActiveCell.FormulaR1C1 = "2"
Range("K9").Select
ActiveCell.FormulaR1C1 = "2"
Range("K9:K48").Select
Selection.Copy
Range("AF1").Select
ActiveWindow.SmallScroll Down:=28
Range("K53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-28
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=28
Range("C53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
End Sub