How to create a Macro-enabled template in Excel?
Supposing you add a VBA macro for a workbook, now you want to save it as a template. With normally method to save as template, it will pop up a warning dialog box, see screen shot below. Actually, we can easily save this workbook as a Macro-enabled template.
- Save current workbook as a macro-enabled template
- Change the default save format as macro-enable templates
- Save selection as mini template (AutoText entry, remaining cell formats and formulas) for reusing in future
Save current workbook as a macro-enabled template
To save a workbook with VBA macro as template, you can do as following:
1. Click the File > Save > Computer > Browse.
2. In the coming Save As dialog box, enter a name for this workbook in the File name box, and click the Save as type box and select Excel Macro-Enabled Template (*.xltm) from drop down list.
Note: When you specify workbook type as Excel Macro-Enabled Template (*.xltm), it will open the default save location of templates automatically.
3. Click the Save button.
Up to present, you have saved the workbook with VBA macros as a macro-enabled template already.
Change the default save format as macro-enable template
Actually, we can change the default save format and save all workbooks as macro-enable templates automatically. Please do as follows:
1. Click File > Options.
2. In the opening Excel Options dialog box, please click Save in the left bar, choose Excel Macro-Enable Template (*.xltm) from the Save files in this format drop down list in the Save workbooks section. See screenshot:
3. Click the OK button.
From now all, any workbook will be saved as macro-enabled template automatically when you clicking File > Save.
Save range as mini template (AutoText entry, remaining cell formats and formulas) for reusing in future
Normally Microsoft Excel saves the whole workbook as a personal template. But, sometimes you may just need to reuse a certain selection frequently. Comparing to save the entire workbook as template, Kutools for Excel provides a cute workaround of AutoText utility to save the selected range as an AutoText entry, which can remain the cell formats and formulas in the range. And then you will reuse this range with just one click.
Related articles:
How to make a read-only template in Excel?
How to protect/lock an Excel template being overwritten with password?
How to find and change default save location of Excel templates?
How to edit/change a personal template in Excel?
How to change the default workbook/sheet template in Excel?Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
