Dealing with worksheet insert error

[Homer@bmwc.b]

the Microsoft KB acknowledges that a 1004 error can occur when the action of copying and inserted a worksheet is performed multiple times in a workbook. MS:KB:Q210684

They provide a proposed fix but it seems like only a partial fix. Their fix is that you can copy the worksheet from an XLT file.

So far so good. This works great if the XLT file contains only one worksheet.

What I have been trying to extend from their method is to be able to programmicly choose which worksheet I want to copy from the template.

Ideally I would like to pass a variable name with the sheet name and get exactly that sheet inserted in my open workbook.

So far I have been flumoxed in all attempts.

One option would seem to be to put every worksheet I might want to copy in its own template file and then programically call for the sheet from that workbook. I think I could do this but find it distasteful because of the number of files I would have to support for a large number of users.

Any ideas?

John Homer
Homer@bmwc.biz

[Paul_Hossler]

Far be it for me to argue with MS, but I'd just open a workbook (doesn't have to be a template), which has all the 'standard' sheets, copy in the one(s) I wanted, and close the source WB

Paul

Something like this (no error checking for existance or duplications)


[vba]
Option Explicit

Sub DriverInsertSheet()
Call InsertSheet("Sheet2", "C:\Documents and Settings\<USERID>\Application Data\Microsoft\Templates\SourceTemplate.xlt")
End Sub

Sub InsertSheet(sSheet As String, sWorkbook As String)
Dim wbDest As Workbook, wbSource As Workbook

Application.ScreenUpdating = False

Set wbDest = ThisWorkbook

Workbooks.Open Filename:=sWorkbook
Set wbSource = ActiveWorkbook


wbSource.Worksheets(sSheet).Copy Before:=wbDest.Worksheets(1)

Call wbSource.Close(False)

wbDest.Activate

Application.ScreenUpdating = True
End Sub
[/vba]

[Homer@bmwc.b]

Thanks!

That seems to work just fine!!!

John Homer

[lucas]

That's a good solution for a lot of problems......thanks Paul.

I just merged all of my xlt files together into one file and created a small addin with a menu to call the one I want using your code. As John says, it works just fine.

[Homer@bmwc.b]

Interesting twist.

Paul had it exactly right. Being an xlt file had nothing to do with the solution. The code he provided helped me accomplish exactly what I wanted until the problem reappeared on insertion of the 20th worksheet.

I'm guessing now but it appears that the problem must be one of what in a different time we used to call garbage collection, not one of the source of the worksheet copy being either the problem or part of the solution.

Anyway, thanks again for the help. It got me over the frustration I had in getting the code right.

John Homer

[lucas]

Be sure to mark the thread solved using the thread tools at the top of the page John.