running through sheets

[kemperohlmeyer]

Can someone help me create a shell code to use that:
-takes a specific sheet within a book
-copies the the entire sheet (or a sufficiently large (a1:q220)
-pastes it onto every page in the book (except the first three & omits blanks)

Thanks,
ko

 

[mbb4331]

Untested. Not sure about blanks, but this will copy the cells on one sheet (CopySheet) to all other sheets not named NoPaste1, NoPaste2 or NoPaste3:

Sub test()

Dim sht As Worksheet

ThisWorkbook.Sheets("CopySheet").Cells.Copy

For Each sht In ThisWorkbook.Worksheets
If sht.Name <> "NoPaste1" And sht.Name <> "NoPaste2" And sht.Name <> "NoPaste3" Then
ThisWorkbook.Sheets(sht).Range("A1").PasteSpecial xlPasteValues
End If
Next sht

End Sub

 

[kemperohlmeyer]

Data mismatch error

Thanks for the help. When i run my edited version (modified for my workbook) nothing really happens. It looks like the macro will copy the sheet asked for but not paste anywhere.

Can someone help?

Dim sht As Worksheet

Sub CopyPasteTest()

ThisWorkbook.Sheets("CopySheet").Cells.Copy
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> "Procedure" And sht.Name <> "Manager List" And sht.Name <> "CopySheet" Then
ThisWorkbook.Sheets(sht).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, skipblanks:=True, Transpose:=False
End If
Next sht
End Sub