Glasgowsmile
Active Member
- Joined
- Apr 14, 2018
- Messages
- 280
- Office Version
- 365
- Platform
- 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.
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