Option Explicit

Private Sub Workbook_Open()

    ' Written by Philip Treacy Apr 2014, http://www.myonlinetraininghub.com/author/philipt
    ' http://www.myonlinetraininghub.com/macro-enabled-excel-templates
    '
    Dim RefNo As Long
    Dim Folder As String
    Dim SheetNum As Integer
    Dim IndexSheet As String
    Dim FilePrefix As String
    Dim FileSuffix As String
                
    Application.ScreenUpdating = False
    
    ' ************************************
    ' Change these values to what you want
    '
    ' *******************************
    ' NOTE the trailing \ for Folder
    ' *******************************
    ' e.g. Folder = "d:\temp\"
    '
    Folder = ""
    IndexSheet = "Invoice"
    FilePrefix = "Inv_"
    FileSuffix = "_Monthly"
    ' ************************************
    
    RefNo = Sheets(IndexSheet).Range("NextIndex").Value

    'Increment the Reference number
    Sheets(IndexSheet).Range("NextIndex").Value = RefNo + 1
    
    'Write new Ref No to sheet
    Range("ThisIndex").Value = RefNo
    
    'Save this workbook
    ThisWorkbook.Save
        
    'Create a new workbook with just 1 sheet
    Workbooks.Add (1)
    
    'Copy sheets from template to new workbook

    For SheetNum = 1 To ThisWorkbook.Sheets.Count
    
        ThisWorkbook.Sheets(SheetNum).Copy After:=ActiveWorkbook.Sheets(SheetNum)
    
    Next
    
    
    'Blank the Next Ref No so it doesn't get saved in the new workbook/sheet
    ActiveWorkbook.Worksheets(IndexSheet).Range("NextIndex").ClearContents
    
    'Delete default sheet from new workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    
    
    'Select Info sheet to make it active
    ActiveWorkbook.Sheets(IndexSheet).Select
    
    'Save workbook with the new Reference Number name
    ActiveWorkbook.SaveAs Folder & FilePrefix & RefNo & FileSuffix & ".xlsx", xlOpenXMLWorkbook
    
    Application.ScreenUpdating = True
    
    'Close the template workbook, we don't want to save any chnages since our last save
    ThisWorkbook.Close Savechanges:=False
    

End Sub