VBA & Macro Problems

How to Automatically Save a Monthly Excel XLSM File Using VBA

Nimra Malik Oct 7, 2026 869 views

Question details

The user needs to automatically save an Excel workbook at the end of every month as a new macro-enabled (.xlsm) file in a specific folder, using a date-formatted filename (e.g., yyyy_mm).

How to Automatically Save a Monthly Excel XLSM File

Product: Excel
Device & OS: not provided
Scenario: Automating the monthly archiving of an Excel spreadsheet with custom date formatting without manual intervention.
Observed behavior: A scheduled process is required to execute a VBA macro that saves the file under a new month-specific name while preserving macro functionalities.

Before you start

Ensure that your Excel workbook is already saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in the Trust Center settings so the automation can run smoothly.

Solution 1Recommended

Use a VBA Macro and Windows Task Scheduler

Write a custom VBA script to save the workbook with the current month's date format, and trigger it automatically using Windows Task Scheduler.

This approach combines Excel's internal VBA capabilities to format the filename and save the file, alongside the Windows operating system's scheduling tool to automate the timing without requiring Excel to be opened manually.

Open the VBA Editor

Open your Excel workbook and press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module and Paste Code

Go to Insert > Module in the top menu. In the new window, paste your macro code. You will need to define your 'savePath' (e.g., "C:\Your\Folder\Path\"), set your 'fileName' using Format(Date,"yyyy_mm"), and execute ThisWorkbook.SaveAs savePath & fileName, FileFormat:=52.

Save and Close

Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) and close the Excel application entirely.

Create a Scheduled Task

Open 'Task Scheduler' from your Windows Start Menu. Click 'Create Basic Task' in the right-hand panel, name it, and set the Trigger to 'Monthly'.

Configure the Task Action

Set the Action to 'Start a program'. Browse to select the Excel executable (excel.exe). In the 'Add arguments' box, enter the full file path to your XLSM workbook enclosed in quotes, then save the task.

Use a VBA Macro and Windows Task Scheduler

Testing the Macro: You can test the macro immediately by placing your cursor inside the code block and pressing F5 in the VBA editor before setting up the Task Scheduler.

Advanced Spreadsheet Automation

Automate and Save Macro-Enabled Workbooks in WPS Office

WPS Office provides robust support for VBA macros, allowing you to automate repetitive tasks like monthly file saving just as easily. Its highly compatible Spreadsheet module works seamlessly with .xlsm files and VBA scripting.

1. Open Your Spreadsheet in WPS Office: Download and launch WPS Office, then open your existing workbook in the WPS Spreadsheet module.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'Macros', or press Alt+F11 to open the VBA Editor.
3. Insert Your Automation Script: Click Insert > Module, and paste your monthly save VBA script into the code window.
4. Save as XLSM: Go to Menu > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your automation scripts are preserved.

Fully compatible with Microsoft Excel .xlsm and .xlsx formats.Robust support for advanced VBA macros and developer tools.Lightweight software with a familiar, easy-to-use interface.Free to download with comprehensive spreadsheet functions.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my macro save the file as a .xlsx instead of .xlsm?

In the VBA SaveAs method, you must explicitly specify the FileFormat parameter when saving macro-enabled workbooks. Using 'FileFormat:=52' in your code ensures the workbook is saved correctly as an Excel Macro-Enabled Workbook (.xlsm).

Will the Windows Task Scheduler run if my computer is asleep?

By default, Task Scheduler will not wake your computer to run a task. You can change this by opening your task properties, navigating to the 'Conditions' tab, and checking the 'Wake the computer to run this task' option.

How can I test the monthly save macro without waiting for the end of the month?

You can temporarily change the VBA 'Date' function in your code to a hardcoded date string, such as "2023_10", or use a test variable. This allows you to verify that the filename formatting and folder paths work correctly before deploying.

Excel & Data Problems