Macro to paste range into all other sheetsClosed

Question

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!!!

rizvisa1 · Answer

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

gmasterv · Answer

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

gmasterv · Answer

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

rizvisa1 · Answer

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

Macro to paste range into all other sheets

4 responses

Similar discussions

Subscribe To Our Newsletter!