Update an Excel Macro-Enabled Template with VBA SaveAs
Question details
The user needs to overwrite an existing Excel macro-enabled template after adding a name to a hidden worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA procedure updates a hidden worksheet and must save those changes back to an existing .xltm template.
- Observed behavior
- SaveCopyAs produces an error when used to replace the template.
Confirm the target folder and .xltm file name, and make sure the workbook containing the updated hidden worksheet is the active workbook. Check that the destination template is writable and is not locked by another Excel session.
Replace the existing .xltm template with VBA SaveAs
Use SaveAs with the macro-enabled template file format when the goal is to replace the existing template rather than save a separate copy.
SaveAs lets you explicitly choose the destination path and file format. Use xlOpenXMLTemplateMacroEnabled for an .xltm template so the file remains macro-enabled.
Temporarily disabling DisplayAlerts allows Excel to overwrite the existing destination without an overwrite prompt. Restore the previous alert setting even if saving fails.
In Excel, select Developer > Visual Basic and open the procedure that adds the name to the hidden worksheet. Locate the SaveCopyAs statement that runs after the worksheet update.
Replace the saving section with the following pattern, or add this procedure in Insert > Module and call it after updating the worksheet: Sub SaveUpdatedTemplate() Dim wb As Workbook Dim previousAlerts As Boolean Dim errorMessage As String Set wb = ActiveWorkbook previousAlerts = Application.DisplayAlerts On Error GoTo SaveFailed Application.DisplayAlerts = False wb.SaveAs Filename:="C:\Users\innov\Desktop\MOLD PM RECORDS\New PM Record.xltm", _ FileFormat:=xlOpenXMLTemplateMacroEnabled Application.DisplayAlerts = previousAlerts Exit Sub SaveFailed: errorMessage = Err.Description Application.DisplayAlerts = previousAlerts MsgBox "The template could not be saved: " & errorMessage, vbExclamation End Sub
Edit the Filename value to match the exact template you intend to replace. The example captures ActiveWorkbook; use ThisWorkbook instead only if the workbook containing the VBA code is also the workbook whose updated worksheet must be saved.
Run the existing update procedure, followed by SaveUpdatedTemplate. Then use File > Open > Browse to open the saved .xltm file itself and verify the added name; if needed, inspect the hidden worksheet through the VBA editor.

Use WPS Spreadsheet for Everyday Excel-Compatible Work
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making migration easy for everyday editing. It does not fix Excel's VBA SaveAs behavior; verify VBA and macro-enabled template support in your WPS version before moving this workflow.

Frequently Asked Questions
Which file format should I use to save an Excel macro-enabled template?
In VBA, call Workbook.SaveAs with FileFormat:=xlOpenXMLTemplateMacroEnabled and a file name ending in .xltm. Through the Excel interface, use File > Save As and select Excel Macro-Enabled Template (*.xltm).
Does a hidden worksheet need to be unhidden before saving?
No. Saving the workbook also saves changes to hidden and very hidden worksheets. Place the SaveAs call after the code that adds the name, and make sure it references the workbook containing that worksheet.
Should I use ActiveWorkbook or ThisWorkbook?
ActiveWorkbook refers to the workbook currently active in Excel. ThisWorkbook refers to the workbook containing the running VBA code. If the code runs from an add-in or a personal macro workbook, ThisWorkbook may not be the template you intend to update.
Why can SaveAs still fail when DisplayAlerts is False?
DisplayAlerts suppresses prompts; it does not create missing folders, grant write permissions, or release file locks. Check the Filename path in the VBA editor, confirm the folder exists, and close any other session using the destination template.
How is SaveAs different from SaveCopyAs?
SaveCopyAs saves a copy without changing the open workbook's identity and does not offer a FileFormat argument. SaveAs saves to the specified destination, lets you select the format, and associates the open workbook with that destination. For this template replacement task, use SaveAs with the .xltm format.




