I need to Sheets.Add a single sheet of a template containing two sheets

Hi,

I have a template which also contains a revision log sheet, which I don't need to import.
The path to the template is

Path = "G:\Stuff\t3.xlt"

The code to import the template is

Set Sheet = Sheets.Add(, , , Path)

This code imports both sheets (and gives a strange message I can't figure).

The two sheets in the template are titled "Sheet1" and "Revision log".

I tried using the

Path = "G:\Stuff\[t3.xlt]Sheet1"

as I thought this would select the specified sheet.

Any help is appreciated.

Add a single sheet

Hi,

In the way you do it you cannot add a specific sheet. Here's some solution that you can adjust it to your subroutine:

' ************************ ' ************************* '

Sub SomeSubroutine()

    Dim strPath As String
    Dim strWshName As String
    Dim oThisWbk As Workbook
    Dim oTempWbk As Workbook

   
    Application.ScreenUpdating = False
   
    On Error GoTo ERROR_HANDLER
   
    strPath = "G:\Stuff\t3.xlt"
    strWshName = "Sheet1"

   
    Set oThisWbk = ThisWorkbook
    Set oTempWbk = Workbooks.Open(strPath)

   
    With oTempWbk
        ' Change to desired paste location (After:= ... | Before:= ...)
        .Worksheets(strWshName).Copy _
            After:=oThisWbk.Worksheets(oThisWbk.Worksheets.Count)
        .Saved = True
        .Close
    End With

   
EXIT_SUB:
    Set oTempWbk = Nothing
    Set oThisWbk = Nothing

   
    Application.ScreenUpdating = True
   
    Exit Sub
   
ERROR_HANDLER:
    ' Some code for error handling
    Err.Clear
    GoTo EXIT_SUB

End Sub

' ************************ ' ************************* '

 

Best regards.