• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Macro Enabled Excel Templates

You are here: Home / Excel VBA / Macro Enabled Excel Templates
Macro Enabled Excel Templates
April 9, 2014 by Philip Treacy

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.

Microsoft Invoice Template

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.

ThisWorkbook 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 = ""

VBA Variables

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.

Named ranges

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.

Exit Sub

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

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

You can download the code in a .xlsm workbook, or the plain text file. You can also copy and paste the code below.

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
    
Macro Enabled Excel Templates

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBATag: Excel VBA
Previous Post:Trusted LocationsTrusted Locations
Next Post:Create Hyperlinked List of Files in a Folder Using VBACreate Hyperlinked List of Files in a Folder Using VBA

Reader Interactions

Comments

  1. ollie brooks

    April 30, 2014 at 1:36 pm

    musn’t lose your url 🙂

    Reply
    • Philip Treacy

      May 1, 2014 at 9:18 am

      Keep coming back here Ollie. Lots more to learn 🙂

      Reply
  2. Sanjiv Daman

    April 15, 2014 at 9:58 pm

    This is really commendable work. Well done.

    Reply
    • Philip Treacy

      April 16, 2014 at 9:11 am

      Thanks Sanjiv. Have you done anything like this yourself?

      Phil

      Reply
  3. Maxime Manuel

    April 10, 2014 at 11:49 pm

    Mynda, I read the newsletter from Phil. Like I said before, you are my Excel Superstar or my supermodeller. You deserve the title of MVP.

    Reply
    • Mynda Treacy

      April 11, 2014 at 8:39 am

      Thank you, Maxime 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.