Adding a new sheet with template settings (Excel VBA)

Question

1. Problem

Every time I start Excel I automatically load macro files from a specific path (in my case it is "C:\Data") - you can set the path in excel settings. I have created functions in these files to calculate some physical quantities. I want to create a macro file (.xlam) in this location, which would work be related to adding a new worksheet depending on the selected (previously created by me) template.

2. Code

At this point, the code looks as follows (code in "ThisWorkbook"):

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    
    On Error GoTo enable_events
    
        Dim template_path As String
        template_path = "C:\Users\UserName\AppData\Roaming\Microsoft\Templates\eng-sheet.xltx"
        Dim wb As Workbook
        
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(template_path)
        wb.Worksheets(1).Copy after:=Sh
        wb.Close False
        Application.DisplayAlerts = False
        Sh.Delete
        Application.ScreenUpdating = True
        
enable_events:
        Application.EnableEvents = True
End Sub

code in "Modules":

Sub Select_The_Sheet_Template()
    Dim MyPath As String
    Dim SaveDriveDir As String
    Dim FileToOpen As Variant
    
    Dim wbTemplate As Workbook ' a varaible to manipulate the template workbook
    Dim sht As Worksheet ' a variable to keep track of a worksheet
    Set sht = ActiveSheet ' remember which sheet is active before the template is opened
    
    SaveDriveDir = CurDir
    MyPath = Application.TemplatesPath
    ChDrive MyPath
    ChDir MyPath

    FileToOpen = Application.GetOpenFilename("Excel Templates (*.xlt*),*.xlt*")
    If FileToOpen <> False Then
        Set wbTemplate = Workbooks.Open(FileToOpen) 'open the template
        wbTemplate.Worksheets(1).Copy after:=sht ' copy the first sheet of the template just after the activesheet
        wbTemplate.Close False 'close the template
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
End Sub

The above code works as follows: it adds a new sheet from the specified template when the "plus button" is pressed, but when I save the file, it has the extension .xltm (because it is loaded as a template). The solution I am looking for would not open the template with the .xltm macro, but the .xltx template and add new sheets based on the macro file, which would be under the path "C:\Data".

3. Question

What would the code look like in the .xlsm file under the path "C:\Data" that would work like the example shown below?

Example: If template "english-ver.xlt" was opened, then the settings of new sheet will be from file "eng-sheet.xlt"; Elseif "spain-ver.xlt" was opened, then add new sheet from file "spain-sheet.xlt"; Else open default sheet version.

Additional Information: I also provide a link where I addressed a similar issue, while the problem described here is suitable for a new thread.

Answer 1

If you change Select_The_Sheet_Template as follows, it should do the trick

Sub Select_The_Sheet_Template()
    Dim wb As Workbook
    Dim templates As String
    templates = " eng-sheet.xlt spain-ver.xlt " ' be sure to keep leading and trailing spaces
    
    Dim sht As Worksheet ' a variable to keep track of a worksheet
    Set sht = ActiveSheet ' remember which sheet is active before the template is opened
    
    ''iterate over open workbooks
    For Each wb In Workbooks
      Debug.Print wb.Name
      If InStr(1, templates, " " & wb.Name & " ") > 0 Then
          'we found one of the templates open
          wb.Worksheets(1).Copy after:=sht ' copy the first sheet of the template just after the activesheet
      End If
    Next
End Sub