running through sheets

kemperohlmeyer

Board Regular
Joined
Oct 4, 2006
Messages
73
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152
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

Board Regular
Joined
Oct 4, 2006
Messages
73
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
 

Forum statistics

Threads
1,136,430
Messages
5,675,803
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top