Change VBA as Tab name changes

[Glasgowsmile]

Below is the code I'm using and the problem is that I plan to duplicate the "Template" tab and rename them but want all the same code but need it to reference the name of that tab in the code (without manually changing it each time) as this document will go to people that won't know how to manually adjust it.

I tried using the Code name of the sheet but it wouldn't work so I'm at a loss as to what my options are.

Private Sub TemplateCode()
   Sheets("Template").Range("D2:V2,D4:S34").ClearContents
   Application.DisplayAlerts = False
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim xRg As Range
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         
         ' Copy ID
         Sheets(1).Range("B11").Copy
         wkbCrntWorkBook.Sheets("Template").Range("A74").PasteSpecial xlPasteValues
         
         ' Copy / Paste Types
         Sheets(1).Range("F17:V17").Copy
         wkbCrntWorkBook.Sheets("Template").Range("D5").PasteSpecial xlPasteValues
         
         ' Copy / Paste Type Data
         Sheets(1).Range("F19:V49").Copy
         wkbCrntWorkBook.Sheets("Template").Range("D7").PasteSpecial xlPasteValues
         
         wkbSourceBook.Close False
      End If
   End With
   
   Range("A1").Activate
   
End Sub

 

[mumps]

Place the template sheet at the end of your workbook so that it is the last sheet. You can then copy it to make the new copy the last sheet and you can then re-name it as in the following code. Change the text in red to the desired sheet name.

Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "NewSheetName"

Each time you run the above code it will always copy the last sheet regardless of the name. Does that make sense? Similarly, you can make the Template the first sheet and when you copy it, place it at the beginning of the workbook and re-name it.

Sheets(1).Copy before:=Sheets(1)
ActiveSheet.Name = "NewSheetName"