How to duplicate Worksheets

[peterfarge]

Hello Newsgroup,

I have created some template worksheets in my Excel VBA projekt and
want to duplicate them. How can I do that without errors?

1. Try: Simply use method copy.
I got this error if I execute this code some times:
Worksheets("Template1").Copy(, Worksheets(Worksheets.count))
'Error: 1004: "Copy Method of Worksheet Class failed"
I have found this help page from MS:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684
But I'm not using named ranges.

2. I have tried to create a new worksheet and place the a range from
the template on it:
Set NewSheet = Worksheets.Add
Worksheets(sTemplateSheet).Range("A1:V127").Copy NewSheet
But If execute this code in a loop to create all new tables, Excel
crashes down.

3. I have create a new Workbook with all template sheets an have copied
the templates also in my main sheet. The same error as in 1. appears.



What can I do to create sheets by template? I there a hint?

 

[Guest]

ActiveSheet.Copy , Sheets(ActiveWorkbook.Worksheets.Count)
this will put the duplicate to the very end...

 

[peterfarge]

Yes, but If I do it in this way I got the Error:
'Error: 1004: "Copy Method of Worksheet Class failed"

 

[Dave Peterson]

Alvin's code worked ok for me.

Did you change it when you tested it?

You may want to post what you tried.

 

[peterfarge]

It works if you want to make 1 or 2 copies. But If you have a big
template table and want to make 40 copies you are running in one of the
errors above.

 

[Guest]

i tried 112 copies without errors...