Change VBA as Tab name changes

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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.
Rich (BB code):
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.

Rich (BB code):
Sheets(1).Copy before:=Sheets(1)
ActiveSheet.Name = "NewSheetName"
 
Last edited:

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top