How to Create a Macro-Enabled Excel Workbook from a Template
Question details
The user needs to distribute macro-enabled Excel workbooks that include UserForms and VBA modules without modifying the VBA project programmatically.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating and distributing custom macro workbooks securely to multiple users.
- Observed behavior
- The goal is to generate new workbooks from a pre-configured macro-enabled template to prevent Excel VBA project security blocks.
Ensure you have fully designed your master workbook with all necessary UserForms and VBA scripts, and saved it in a trusted directory as an .xlsm or .xltm file.
Deploy Using a VBA FileCopy Script
Use a deployment script to duplicate a pre-configured macro-enabled template, completely bypassing the need for programmatic access to the VBA project.
Dynamically generating VBA modules or UserForms using code often triggers Excel's Trust Center security blocks. By creating a master template and utilizing the FileCopy command, you can reliably generate functional user workbooks without encountering security errors.
Design your complete Excel workbook with all required UserForms and VBA code. Save it to a known directory, for example: C:\MyTemplates\UserTemplate.xlsm.
Create a new Excel workbook that will serve as the distributor. Press Alt + F11 to open the Visual Basic editor, click 'Insert', and select 'Module'.
Define your template path and the output path. Use the FileCopy command to duplicate the file. For example: FileCopy "C:\MyTemplates\UserTemplate.xlsm", "C:\DeployedFiles\UserWorkbook_" & Format(Now,"yyyymmdd") & ".xlsm".
Add a line to briefly open and save the newly created file so it initializes properly: Workbooks.Open(newFilePath).Close SaveChanges:=True.
Press F5 or run the macro from the Excel 'Developer' tab. A new, fully macro-enabled copy of your template will appear in the destination folder.

Create and Manage Macro Templates Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for VBA macros, enabling you to build, edit, and distribute macro-enabled templates (.xlsm / .xltm) with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and start a new blank spreadsheet or open an existing data file.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA environment.
- 3. Build Your Template: Insert your modules and UserForms, write your VBA scripts, and design the workbook layout.
- 4. Save as Macro-Enabled File: Click 'Menu' > 'Save As' and choose 'Microsoft Excel Macro-Enabled Workbook (*.xlsm)' or 'Macro-Enabled Template (*.xltm)' to distribute it safely.

Frequently Asked Questions
Why shouldn't I use VBA to programmatically write other VBA code?
Writing VBA code with other VBA code requires enabling 'Trust access to the VBA project object model' in Excel's Trust Center. Because this is disabled by default for security reasons (to prevent macro viruses), distributing workbooks that rely on this method usually results in runtime errors for end users.
What is the difference between an .xltm and an .xlsm file for this method?
An .xltm file is specifically designated as a template; opening it manually forces the user to save it as a new document. An .xlsm is a standard macro-enabled workbook. When using a script to duplicate a file behind the scenes, both formats work, but .xlsm is generally easier to handle as a raw source file.
Can I dynamically rename the generated workbooks?
Yes. In your VBA FileCopy script, you can concatenate variables, timestamps (using the Format function), or user inputs to generate unique file names for every duplicated workbook, preventing file overwrite issues.




