Adding a worksheet from an external workbook using VBA

Question

How do I use the Worksheet.Add function to add a worksheet from an external workbook?

When I try to add a worksheet from an external workbook I get an error:

Method 'Add' of object 'Sheets' failed on the third line`

Here is my code:

Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Worksheets.Add(After:=Sheet1, Count:=2,Type:="\\SharedDrive\Worksheet\Student.xltm")
Application.ScreenUpdating = True

Have you looked to see if it works with a local file, rather than the share you've used in your example? — jon
– jon, Commented Jan 17, 2012 at 19:24

Siddharth Rout · Accepted Answer · 2012-01-17 20:20:59Z

3

The "Type:=" has to be a template if you are inserting from another file.

The syntax is

expression.Add(Before, After, Count, Type)

Where

Type is 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.

For example

Set ws = Worksheets.Add(After:=Sheet1, Count:=2, _
Type:="\\SharedDrive\Worksheet\Student.xlt")

or

Set ws = Worksheets.Add(After:=Sheet1, Count:=2,_
Type:="\\SharedDrive\Worksheet\Student.xltm")

or

Set ws = Worksheets.Add(After:=Sheet1, Count:=2,_
Type:="\\SharedDrive\Worksheet\Student.xltx")

xlt - Excel 2003 Template

xltx - Excel 2007/2010 Template

xltm - Excel 2007/2010 Macro-Enabled Template

edited Jan 17, 2012 at 20:20

answered Jan 17, 2012 at 20:09

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user793468 Over a year ago

I updated the Type but still get the same error. Do I need to mention sheet name somewhere (sheet that I want to insert from the template)? I am trying to insert this sheet in a macro enabled workbook (I tried saving the template as .xltm but still get the same error). Am I missing something here? I have updated my code above.

Siddharth Rout Over a year ago

Let's take it 1 step at a time :) 1) Create a new template and save it in your local drive. Test it with that File. Does it work? 2) If it does then copy the same template to the shared drive and test it again. Does it work? We will take it from there :)

Collectives™ on Stack Overflow

Adding a worksheet from an external workbook using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related