Create a Timestamped Excel Workbook Backup Using a VBA Macro
Question details
The user needs a VBA macro that creates a backup copy of the active workbook in a separate folder, with the current date and time included in the backup filename.

- Product
- WPS Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to automate workbook backups so each copy is saved to a fixed backup folder and can be identified by date and time.
- Observed behavior
- A timestamped backup copy should be created in the separate folder while the active workbook remains open and unchanged.
Before running the macro, confirm that the backup folder already exists because SaveCopyAs does not create folders automatically. Also save the workbook once so the active workbook has a file name and extension that the macro can read.
Create a Timestamped Workbook Backup with a VBA Macro
Use this solution when you want an automated, repeatable backup named with the current date and time so each copy is easy to identify.
The macro saves the active workbook with SaveCopyAs. It inserts a timestamp between the original file name and extension, so the active workbook is not overwritten.
In WPS Spreadsheets or Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, right-click the workbook project, choose Insert, and click Module to open a blank code window.
Paste the following macro, then update the backup folder path if necessary: Sub CreateBackup() Const strFolder As String = "C:\Backup\" Dim strFile As String Dim lngPos As Long strFile = ActiveWorkbook.Name lngPos = InStrRev(strFile, ".") strFile = Left(strFile, lngPos - 1) & Format(Now, "_yyyymmdd_hhmmss") & Mid(strFile, lngPos) ActiveWorkbook.SaveCopyAs strFolder & strFile End Sub
Save the workbook as a macro-enabled file format so the VBA code remains available the next time you open it.
Press Alt+F8, select CreateBackup, and click Run. The timestamped backup copy is saved in the specified backup folder.

Create Timestamped Workbook Backups with WPS Spreadsheets VBA
WPS Spreadsheets supports the same SaveCopyAs VBA workflow, letting you automatically save timestamped workbook copies to a separate folder while keeping the original file intact.
- 1. Download and install WPS Office: Go to the official WPS website, download WPS Office, install it, and launch WPS Spreadsheets.
- 2. Open the macro-enabled workbook: Click Menu > Open in WPS Spreadsheets and select the Excel workbook that contains or will contain the backup macro.
- 3. Open the VBA editor: Press Alt+F11 to open the Microsoft Visual Basic for Applications editor, then insert a new module from Insert > Module.
- 4. Add the backup macro: Paste the CreateBackup VBA code into the module and update the backup folder path to an existing folder on your PC.
- 5. Run the macro: Return to the workbook and press Alt+F8, choose CreateBackup, and click Run to create the timestamped backup copy.

Frequently Asked Questions
Does the macro create the backup folder automatically?
No. SaveCopyAs does not create folders. Create the backup folder, such as C:\Backup\, before running the macro, or add a MkDir statement to the VBA code.
Can I use a different timestamp format in the backup filename?
Yes. Change Format(Now, "_yyyymmdd_hhmmss") to another valid date-time pattern, but avoid characters such as / or : that are invalid in file names.
Will this macro overwrite my original workbook?
No. SaveCopyAs creates a separate copy. The active workbook remains open and unchanged, and the timestamped file is written to the backup folder.
Do I need to save my workbook as a macro-enabled file?
Yes. In Excel or WPS Spreadsheets, save the file as a macro-enabled workbook so the VBA code is retained; otherwise the macro may be removed.
Can I back up only one worksheet with this macro?
No, this macro backs up the entire active workbook. To export a single sheet, use a different routine such as copying the sheet to a new workbook and saving that file.




