VBA & Macro Problems

How to Automatically Create a New Monthly Excel Workbook Copy

Olivia Miller Sep 25, 2026 871 views

Question details

The user needs a method to automate the duplication of a monthly workbook, updating the filename to reflect the new month and year, and clearing old numerical values while retaining formulas and formatting.

Product: Excel
Device & OS: not provided
Scenario: Preparing recurring monthly reporting templates, such as a wages workbook, where manual duplication and data clearing are tedious.
Observed behavior: The user wants to replace a manual rollover process with an automated workflow that generates the next month's clean template automatically.

Before you start

Before automating this process, create a backup copy of your original workbook and ensure your spreadsheet software's security settings allow macros to run.

Solution 1Recommended

Use an Excel VBA Macro to Automate Monthly Workbook Duplication

A VBA macro is the most efficient way to automatically duplicate your active workbook, rename it with the next month's date, and clear specific data ranges instantly.

VBA (Visual Basic for Applications) allows you to write a script that performs all these actions in a single click. You can target specific cells so that only hardcoded numerical values are deleted, leaving all your formulas, charts, and layouts perfectly intact.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic Editor. Go to Insert > Module to create a new blank module.

Write the Duplication and Rename Script

In the module, write a script utilizing ActiveWorkbook.SaveCopyAs. Use the DateAdd and Format functions to dynamically generate the next month's name for the new file path.

Define the Data to Clear

Within the same macro, open the newly created copy and use Range("YourRange").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents to selectively delete only numerical values without touching formulas.

Run and Assign the Macro

Save your original file as a Macro-Enabled Workbook (.xlsm). Insert a Shape or Button into your worksheet, right-click it, and select 'Assign Macro' to trigger this process with one click.

Use an Excel VBA Macro to Automate Monthly Workbook Duplication

Test Your Macro First: Always test your VBA script on a dummy file first to ensure it clears the correct numerical values and does not accidentally overwrite or delete your structural formulas.

Solution 2

Automate the Process using Microsoft Power Automate

If you prefer a no-code cloud solution and store your files in OneDrive or SharePoint, Power Automate can schedule this task to run entirely in the background.

Automate with WPS

Automate Monthly Workbooks with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to run powerful scripts that generate monthly copies, clear data, and format reports automatically without any hassle.

1. Open Your Template: Open your existing Excel .xlsm or .xlsx template directly in WPS Spreadsheet.
2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab on the ribbon and click 'Visual Basic' to launch the VBA Editor.
3. Insert the Macro: Paste your monthly duplication macro, ensuring the specific ranges you want to clear are correctly defined in the code.
4. Run the Automation: Save the workbook and run the macro directly within WPS to flawlessly generate your new monthly workbook.

Seamlessly compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv).Built-in robust VBA editor in WPS Office to run and write your existing Excel macros.Lightweight software that processes heavy automation scripts quickly and reliably.Cost-effective alternative featuring a highly familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I keep my formulas intact while clearing numerical data?

Yes. When writing your VBA macro, you can use SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents. This specific command targets only hardcoded numbers, ensuring all your formulas and cell formatting remain untouched.

How do I dynamically add the next month's name to the filename?

In your VBA script, you can use the DateAdd and Format functions together. For example, Format(DateAdd("m", 1, Date), "mmmm yyyy") will automatically output the upcoming month and year as a text string to use in your filename.

Will saving the new copy overwrite my current workbook?

Not if you use the ActiveWorkbook.SaveCopyAs method in your code. This method creates a duplicate file with your newly specified name while leaving your currently open template workbook completely unchanged.

Can this automation run without me opening the Excel file?

Standard VBA macros require the workbook to be opened (either manually or via another script) to execute. To fully automate this in the background without opening the file, a cloud-based solution like Power Automate is required.

Excel & Data Problems