copy method of worksheet class failed

[vince.bonnici]

I have a workbook with 2 sheets: Summary and Template. On the Summary
sheet is a list of people in a range called emplist. the macro below
copies the tempate worksheet and renames it to each of the employees
listed in emplist. The problem is that is always fails after adding
the 45th sheet. Here is the VBA, can anyone suggest a better way?

Thanks in advance.

Sub copysheet()
Dim cell As Range, Rng As Range

Set here = ThisWorkbook.ActiveSheet
shtname = ActiveSheet.Name

With Worksheets("Summary")
Set Rng = .Range(.Range("emplist"), .Range("emplist").End(xlDown))
End With
For Each cell In Rng
If cell = "TM" Then
ElseIf cell = "Template" Then
Else
Sheets("Template").Copy AFTER:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value

End If

Next
Worksheets(shtname).Select
here.Select
End Sub

 

[Jon Peltier]

I've run into this problem. What I do instead of copying the template sheet
from a workbook is to have the template sheet saved as a one-sheet workbook
template. Then I add it to the target workbook using:

MyWorkbook.Worksheets.Add c:\Path\TemplateFileName.xlt

See help:

Add method as it applies to the Sheets and Worksheets objects.
Creates a new worksheet, chart, or macro sheet. The new worksheet becomes
the active sheet.

expression.Add(Before, After, Count, Type)

expression Required. An expression that returns one of the above objects.

Before Optional Variant. An object that specifies the sheet before which
the new sheet is added.

After Optional Variant. An object that specifies the sheet after which the
new sheet is added.

Count Optional Variant. The number of sheets to be added. The default
value is one.

Type Optional Variant. Specifies the sheet type. Can be one of the
following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet,
or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing
template, specify the path to the template. The default value is
xlWorksheet.




- Jon

 

[Jon Peltier]

If you really need to copy from the existing sheet, and a static template
won't do, just add a blank sheet, then copy the cells of the sheet you need
copied, and use paste or paste special to get the content into the blank
sheet.

- Jon

 

[vince.bonnici]

I have to leave the tempate in the workbook because each new sheet will
pull information from the Summary sheet, so I used your add worksheet
suggestion and it worked great. I was able to add over 160 sheets and
each renamed based on the list on the Summary sheet.

Thanks for your help!

 

[Dave Peterson]

I think the OP will have to use:

myWorkbook.Sheets.Add Type:="c:\Path\TemplateFileName.xlt"

I don't think it'll work with worksheets (and a couple of "'s added, too <bg>)

 

[Jon Peltier]

Isn't that pathetic? I use this frequently, but still get it wrong.

- Jon

 

[Dave Peterson]

I'm a member of that same club!

Isn't that pathetic? I use this frequently, but still get it wrong.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______