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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top