VBA & Macro Problems

Create a Timestamped Excel Workbook Backup Using a VBA Macro

Emma BrownEmma Brown Nov 13, 2026 869 views

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.

Create a Timestamped Excel Workbook Backup Using a VBA Macro
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In WPS Spreadsheets or Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.

2
Insert a new module

In the Project Explorer, right-click the workbook project, choose Insert, and click Module to open a blank code window.

3
Paste and adapt the backup macro

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

4
Save as a macro-enabled workbook

Save the workbook as a macro-enabled file format so the VBA code remains available the next time you open it.

5
Run the macro

Press Alt+F8, select CreateBackup, and click Run. The timestamped backup copy is saved in the specified backup folder.

Create a Timestamped Workbook Backup with a VBA Macro
Backup created: Check the backup folder for a file named like Book1_20250512_143000.xlsx. The original workbook stays open and unchanged.
WPS VBA macro support

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. 1. Download and install WPS Office: Go to the official WPS website, download WPS Office, install it, and launch WPS Spreadsheets.
  2. 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. 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. 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. 5. Run the macro: Return to the workbook and press Alt+F8, choose CreateBackup, and click Run to create the timestamped backup copy.
Runs the same SaveCopyAs VBA backup workflow as Microsoft Excel.Opens and saves macro-enabled .xlsm and .xls files for Excel compatibility.Free, lightweight Office suite with a familiar ribbon and built-in VBA editor.Automates timestamped backups without changing the active workbook.
microsoft office alternative - wps office

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.