Why does copying a worksheet from Excel Add-in into the ActiveWorkbook takes inordinate amount of time?

In my 2007 Excel Add-in, I have a routine that copies a worksheet from the add-in to the users ActiveWorkbook. When I run the routine, it takes 15-20 seconds to execute this line of code:

ThisWorkbook.Sheets(roadwayQuantTemplate).Copy 开发者_Go百科Before:=ActiveWorkbook.Sheets(1)

This alternate version has the same delay:

ThisWorkbook.Sheets(roadwayQuantTemplate).Copy Before:=Worksheets(1)

Does anyone have any Idea why it takes so long to execute this line? I have another routine that adds a blank worksheet (then modifies it) and the following line of code excecutes with out any delay:

ActiveWorkbook.Worksheets.Add Before:=Worksheets(1)

Should the Copy method really take that much longer to execute than the Add method? There is not a lot of data in the template worksheet that I am copying, only a few cells that make up the header of the worksheet and less than 100 empty cells with formatting applied.

Any ideas?

wild guess ...

sheet copy will copy VBA code attached to that sheet, so if for example your sheet had the following code (or any other code that might trigger upon copying), then you'd see a delay:

Private Sub Worksheet_Activate()
Dim i As Long
For i = 1 To 100000
    Debug.Print "hello again"
Next i
End Sub

Sheet copy also picks up format, pictures, charts etc. If only cell values are needed, fastest is to assign the Worksheet.UsedRange of the worksheet to a variant array, and then assign that array back to a target range on the new sheet (of the same dimensions).

It may be faster to use a standalone template, rather than a sheet within the add-in. The syntax is

Worksheets.Add(Type:=TemplatePathAndFileName)