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.

 

[Erantes]

You'll want to refrain from ActiveWindow, ActiveSheet, ActiveCell when you are trying to run code on multiple sheets.

Hi thanks fir reply so does that mean that in my case it isn't possible to achieve what I'm after?

 

[kevin9999]

It is possible, but could you give us the names of the 3 sheets involved (in order as per you posted macros)

 

[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

 

[Erantes]

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

Hi

 

[Erantes]

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

Hi Kevin this is working fine apart from one thing. The first cell in each sheet K9 is not changing to the new figure but the rest is fine. When I run the macros 1000, 6.4 and 2 show up in K9 instead of new figures.

 

[kevin9999]

K9 is not changing to the new figure but the rest is fine. When I run the macros 1000, 6.4 and 2 show up in K9 instead of new figures.

What are the "new figures" meant to be? According to your code in post #9, this:

Range("K9").Select
ActiveCell.FormulaR1C1 = "1000"

and this

Range("K9").Select
ActiveCell.FormulaR1C1 = "6.4"
Range("K9").Select
ActiveCell.FormulaR1C1 = "6.4"

and this

Range("K9").Select
ActiveCell.FormulaR1C1 = "2"
Range("K9").Select
ActiveCell.FormulaR1C1 = "2"
Range("K9").Select
ActiveCell.FormulaR1C1 = "2"

would have put those figures in K9 of the respective sheets. What are the figures meant to be, and where do they come from?
It would be better if you could provide copies of your sheets using the XL2BB add in, or better still, share your file via Google Drive, Dropbox or similar file sharing platform showing a before-and-after scenario so I know what your aim is.

 

[Erantes]

What are the "new figures" meant to be? According to your code in post #9, this:

and this

and this

would have put those figures in K9 of the respective sheets. What are the figures meant to be, and where do they come from?
It would be better if you could provide copies of your sheets using the XL2BB add in, or better still, share your file via Google Drive, Dropbox or similar file sharing platform showing a before-and-after scenario so I know what your aim is.

Hi I took out the first line on the sheet and works perfect now thankyou.

 

[kevin9999]

Happy it works for you now, although I don't understand how taking out the first line makes any difference to what my code puts in cell K9 of each sheet?! Still, as long as it works now

 

[Erantes]

Happy it works for you now, although I don't understand how taking out the first line makes any difference to what my code puts in cell K9 of each sheet?! Still, as long as it works now

Sorry not very clear, what I did was take out the lines with the 1000, 6.4 and the 2 in them. Now k9 to k48 copies perfectly.

 

[kevin9999]

OK