Making a copy of a sheet within an ADDIN
Posted by Jack on December 04, 2001 1:53 PM
I have an Excel 97 ADDIN which has 3 worksheets, one of which is a template I want to export. I want to copy this sheet within the ADDIN (creating a 4th sheet), rename it, then move it to the users active workbook. The procedure works fine as an XLS file but not as an ADDIN
The line of code is:
ThisWorkbook.Sheets("Template").Copy After:=ThisWorkbook.Sheets(3)
In the xls it creates "Template (2)" as a 4th sheet. As an ADDIN I get "Runtime error 1004 - Copy method of worksheet class failed"
I tried everything I can think of including Walkenbach's Power programming, but his examples only include copying a sheet from the addin to the active workbook , or to a new workbook, which I can do.
Any suggestions?
Posted by Ivan F Moala on December 04, 2001 8:26 PM
Why not just copy it over directly ??
eg.
Sub copysheet()
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("Template")
Sh.Copy Before:=ActiveWorkbook.Sheets("Sheet1")
ActiveSheet.Name = "Testing"
Set Sh = Nothing
End Sub
Ivan
Posted by Jack on December 05, 2001 8:12 AM
It will generate error if sheet exists
I have tried it that way, but you run into trouble (and an error)if that sheet name already exists. Then you have to cycle through the sheets over and over, each time revising the possible name until it does not exist before you can rename it. If you create it in the ADDIN and copy it, and it exists in the active workbook, then Excel will add (1) suffix for the first copy, (2) the next time, etc.
I have a routine that can do the same thing, but it is much cleaner and faster if Excel does it.
