Macro Enabled Excel Templates

What You Can Do With Macro Enabled Templates

This is up to you. Using a template allows you to design a workbook layout to look how you want. Using macros gives you the power to customize that template.

Let's say you are creating invoices and each invoice has a new, unique number. VBA can keep track of those numbers, increment them for each new invoice and then insert the number into the new workbook.

Or you have a report that you run for employees/products/departments (or whatever) and you want to pre-populate fields in the report dependent on some choices you make.

Imagine you are creating a report which shows the sales made by your staff. When you open the macro enabled template, your macro asks you which employee you want to run the report for, and then based on your answer, the template is populated with information specific to that employee like their phone number, employee ID, their sales for last period, sales to date, and their favorite TV show (Game of Thrones?). OK, maybe not their favorite TV show but hopefully you get the idea.

Excel Templates Didn't Do What I Want

I'm not really going to use the .xltm macro enabled template file format, I'm going to use a .xlsm macro enabled workbook as my template.

The way the .xltm templates work didn't really suit what I wanted to do. A .xltm creates a new workbook, but it keeps any VBA code you've written in the template, I don't want that. Once my code has run I want it removed from the workbook I create. Of course you can create macro enabled workbooks from templates, it's up to you and what you want to achieve.

I also want to modify the template file once I open it and then save this so that I can use the modified template for my next workbook. If I'm trying to keep track of something like a number sequence that must be incremented each time the template is opened, I need to save this so I can use it next time. I found the .xltm sometimes giving me unpredictable results when I was trying to save this information back to the .xltm template.

Real World Example : Invoicing

I've just used Microsoft's Simple Invoice template which is an Excel template file with the .xltx file extension.

I've opened this file and then saved it as a .xlsm, a macro enabled workbook.

When the template opens we are going to use the Workbook_Open event to trigger some VBA code, so our code must be put into the ThisWorkbook code module, not into a standard code module.

The Algorithm

This is what I want the code to do :

1. Get the last invoice number, which is stored in the template. This becomes the new invoice number.
2. Increment the invoice number and save it in the template, so we can use it for the next invoice.
3. Create a new workbook.
4. Copy the contents of the template to the new workbook
5. Save the new workbook using a naming convention I specify.
6. Close the template

How the Code Works

VBA Events

We are using the Workbook_Open event to trigger our code. When the template is opened, our code runs.

Variables

I'm using a few variables to control what is happening.

Folder specifies where to save the newly created workbooks. If you leave it blank then they will be created in the same folder that the template file is in.

IndexSheet is the name of the sheet where I am storing invoice numbers. In your case you can change this to whatever the sheet name is where you want to store your information.

FilePrefix and FileSuffix are text that are used to name the new files. As you can see I have set FilePrefix to Inv_ and FileSuffix to _Monthly. If my invoice number is 12345, then the new filename is Inv_12345_Monthly.xlsx. You can specify one of both FilePrefix and FileSuffix as null strings i.e. FilePrefix = "" or FileSuffix = ""

Copying the Template Contents

If I used a 'proper' template (.xltx or .xltm) then the new workbook would be created with the same layout as the template. But as I've already said using the .xltm format didn't do what I needed. So I have to copy the contents of my template to the new workbook. It's not hard and is done in the blink of an eye.

Named ranges

I'm using two names to refer to the reference numbers I use to create the invoices. NextIndex refers to cell L7 and ThisIndex refers to cell L8.

This Code is Specific to my Needs

But that doesn't mean you can't alter it to do what you want. Of course you will probably want to do something completely different, but seeing how I have done this will hopefully give you an idea of how to achieve what you want. If you get stuck, let me know and I'll see if I can help.

Modifying This Code

Arrgh, I can't open the file, it keeps closing on me. That'll be the Workbook_Open event firing.

When you are asked to enable macros, don't, you can then open the file.

But, with macros disabled I can't test it, I hear you say. True, so once you open the template with macros disabled, below the Workbook_Open declaration, enter Exit Sub, save and close the file.

Next time you open it, enable macros and when the Workbook_Open event fires, it will immediately exit and leave you with a workbook where the macros are enabled and you can alter and test it how you like.

Trusted Locations

To stop Excel asking you if you want to enable macros each time you open the template, put it into a trusted location

The VBA Code

Option Explicit

Private Sub Workbook_Open()

    ' Written by Philip Treacy Apr 2014, https://www.myonlinetraininghub.com/author/philipt
    ' https://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