Copy Template Macro

mits

New Member
Joined
May 2, 2003
Messages
16
I am trying to copy a template worksheet called "Template" to worksheets that already exist but are blank. The reason I have worksheets alreay their is that I used a macro to create worksheets based on the a list that I had. Now that I have 50 worksheets, I would them to be formatted with the column widths and formulas from the template. I created a macro using the "record macro" feature and it works but it always copies the template to the worksheet that I created the macro with. here is the macro:

Sub Copy_template()
'
' Copy_template Macro
'
' Keyboard Shortcut: Ctrl+t
'
Sheets("Template").Select
Cells.Select
Selection.Copy
Sheets("6E4P9").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End Sub

The line "Sheets("6E4P9").Select" is what I need to fix. 6E4P9 is the worksheet name of the sheet I was on when I created the macro. I need to change it to the current worksheet. This will allow the macro to copy the template to the current worksheet that I am going to work on.

Please help

Thanks in advance
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Ceduljko

Board Regular
Joined
Dec 28, 2004
Messages
58
Mits,

Maybe you should re-work your process and copy the already pre-formatted Template sheet 50 times. That's what the templates are for :)

On the other hand, you have to loop through all the sheets except the Template one and apply your code:

Sub Copy_Template()
Dim ws As Worksheet
For Each ws In ActiveWorkbook
if ws.name()<>"Template" then
Sheets("Template").Select
Cells.Select
Selection.Copy

' Change this: Sheets("6E4P9").Select
ws.Select

Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
end if
Next
End Sub

Not very nice code, but it should work.

C.
 

mits

New Member
Joined
May 2, 2003
Messages
16
Thanks for the help but....

I copied the code but it didn't work. The debug message highlights the line:

For Each ws In ActiveWorkbook

I am not a scripter so I don't have the faintest idea what to do.

Ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,229
Members
412,448
Latest member
ManuW
Top