Macro to paste range into all other sheets

Closed
gmasterv - Mar 24, 2010 at 01:21 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Mar 24, 2010 at 09:19 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 24, 2010 at 06:44 AM
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
0
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 24, 2010 at 09:19 AM
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
0