copy method of worksheet class failed
 

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

copy method of worksheet class failed
 

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

copy method of worksheet class failed
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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

copy method of worksheet class failed
 

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!

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


"Jon Peltier" wrote in message
...
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

copy method of worksheet class failed
 

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 wrote:

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


--

Dave Peterson

copy method of worksheet class failed
 

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
_______


"Dave Peterson" wrote in message
...
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 wrote:

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


--

Dave Peterson

copy method of worksheet class failed
 

I'm a member of that same club!

Jon Peltier wrote:

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
_______

"Dave Peterson" wrote in message
...
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 wrote:

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


--

Dave Peterson

--

Dave Peterson