Macro to paste range into all other sheets

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I know this should be much easier than I'm making it. I'm simply tring to loop through all existing sheets in one workbook and paste a section from a "Master" tab into each sheet. Something like:

Sub Macro1()'
Sheets("Master").Select
Range("A1:E10").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveSheet.Paste
End Sub

But Sheet1, Sheet2, Sheet3 is an unlimited number of sheets. I've done formating using:

For Each Sht In Sheets
'formating
Net Sht

but I can't seem to paste a range correctly. Help I've been trying for 3 hours and I'm totaly brain dead...THANK YOU!!!

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I have NOT TESTED this, but I think it will will

Sub Macro1()'
Sheets("Master").Select
Range("A1:E10").Select
Selection.Copy

For Each Sht In Sheets
if (sht.name <> "Master") then
sheets(sht).select
ActiveSheet.Paste
End if
Net Sht

End Sub
Thanks for trying but it's not working (even after correcting the minor typo of "Net Sht" to Next Sht. I'm still getting an error on sheets(sht).Select which seems to be the heart of my problem. I don't now how to select each "Sht" without calling it by actual tab name, not a variable
nevermind! I made a slight change and it worked!! thanks again. final code:

Sub Macro1()
Sheets("Master").Select
Range("A1:E10").Select
Selection.Copy

For Each sht In Sheets
If (sht.Name <> "Master") Then
Sheets(sht.Name).Select
ActiveSheet.Paste
End If
Next sht

End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
My bad. I had Sheets(sht).select. It should have been sht.select

Ok this is tested. I am presuming that you want to paste in the same range on other sheets (A1:E10) so I am selecting cells(1, "A") in the loop.

Sub Macro1() '
Sheets("MASTER").Select
Range("A1:E10").Select
Selection.Copy

For Each sht In Sheets
If (sht.Name <> "MASTER") Then

sht.Select
Cells(1, "A").Select
ActiveSheet.Paste
End If
Next sht

End Sub