worksheets.add error 2

[claudehenri]

Hi

I'm trying to add a template to my workbook but I get this error

Run time error '1004'
Method 'Add' of object 'Sheets' failed

my code is

Worksheets.Add(After:="Geometry & Loads", Count:=x, _
Type:=xlWorksheet) = ThisWorkbook.Path & "\Wave.xlt"

I also tried

Worksheets.Add After:="Geometry & Loads", Count:=x, _
Type:=ThisWorkbook.Path & "\Wave.xlt"

x has been set to 6,"Geometry & Loads" does exist in the file I want to insert into, and Wave.xlt is in the specified folder.

the code below gives a slightly different error message (1004) "Add Method of Sheets class failed"
SaveFile.Worksheets.Add After:="Geometry & Loads", _ Count:=x, Type:=ThisWorkbook.Path & "\Wave.xlt"

The help only says that to insert a template to specify the path which i have done but there is not example of this. (used record macro to get to where i my code is now)


Could anyone point out the mistake i'm making

Claude-Henri

 

[claudehenri]

Sorry forgot to say the "SaveFile" is set to the workbook i want to insert this sheet into.

C-H.

 

[PHV]

And this ?
Worksheets.Add After:=Worksheets("Geometry & Loads"), Count:=x, _
Type:=ThisWorkbook.Path & "\Wave.xlt"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[claudehenri]

I think you are correct that i missed the worksheets( ) part of the command but I am still getting the error.

C-H.

 

[combo]

The 'Type' in Worksheets.Add specifies sheet type, which can be any of XlSheetType enumerated constants (and some others...), not a workbook. So you need two steps:
- create workbook using template
- move sheets to your workbook

The code:
Set wbkTemp=Workbooks.Add(ThisWorkbook.Path & "\Wave.xlt")
wbkTemp.Sheets.Move After:=Thisworkbook.Sheets("Geometry & Loads")
Set wbkTemp=Nothing

combo

 

[claudehenri]

Thanks that works fine. One question though is that when I record a macro it doesn't seem to take 2 steps for this process (I have to place the file in the templates folder to do this)

what i do is right click the sheet tab and choose insert and there is my template. This is what the recorder generates

Sheets.Add Type:= _
"C:\Documents and Settings\claude\Application Data\Microsoft\Templates\Wave Sheet.xlt"

the only real difference is the "After:=" property is missing

any ideas on this?

 

[combo]

OK, seems that it is possible to use template with 'Add'. However, it works only for 'Sheets' collection. 'Count' causes error, you add a whole template. 'Before' and 'After' specify objects, so objects should be there. This can be added in code by hand.
The syntax in your case:

Sheets.Add After:=Worksheets("Geometry & Loads"), Type:=ThisWorkbook.Path & "\Wave.xlt"

combo

 

[claudehenri]

Thanks that makes a bit of sense to me that I can use the count. I was hoping I could do it all in one line but I suppose that is what I'm here for.

Claude-Henri