Open the template book and then saveas. The template will not change and now you can carry out your changes to the new workbook which is active.
Sub OPENbook()
'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
'Show the dialog box
.Show
'Store in fullpath variable
Dim fullpath As String
fullpath = .SelectedItems.Item(1)
End With
'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".xls") = 0 Then
Exit Sub
End If
'Open the file selected by the user
Workbooks.Open fullpath
'Save opened workbook as new workbook
MsgBox "This new workbook will be saved as Consolidated Commissions Statements " & Format(Now, "dd mmm hh mm") & ".xlsx"
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Consolidated Commissions Statements " & Format(Now, "dd mmm hh mm"), xlWorkbookDefault
'Now make your changes
'FORMAT THE NEW WORKBOOK
' Rename the current sheet
ActiveSheet.Name = "Commissions Data"
' Create, name, and color the other needed sheets
Sheets.Add(After:=Sheets("Commissions Data")).Name = "Client Distribution"
Sheets.Add(After:=Sheets("Client Distribution")).Name = "Issuer Distribution"
Sheets("Commissions Data").Tab.ColorIndex = 3
Sheets("Client Distribution").Tab.ColorIndex = 4
Sheets("Issuer Distribution").Tab.ColorIndex = 5
Sheets("Commissions Data").Select
'Copy activesheet to the new workbook
'ActiveSheet.Copy <<<<<<<<<<<<<<<<<<<<<<<<<<<This will copy the sheet to new workbook!!!
' Close the saved copy
ActiveWorkbook.Save
MsgBox "It is saved as " & ActiveWorkbook.FullName & vbLf & "Press OK to close it"
ActiveWorkbook.Close 0
End Sub
A word of caution: A previously created workbook with this name will be over-written. To stop this I've put a time stamp on the file.