| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy method of worksheet class failed | Excel Programming | |||
| copy method of worksheet class failed | Excel Programming | |||
| Copy Method of WorkSheet Class Failed! | Excel Programming | |||
| Copy Method of Worksheet Class Failed | Excel Programming | |||
| Copy Method of Worksheet Class Failed | Excel Programming | |||
Threaded Mode
