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, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
wrote in message
ups.com...
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