Excel VBA: How to Save a Backup Copy of a Workbook

To save a backup copy of the workbook that contains your VBA code, use ThisWorkbook.SaveCopyAs fullPath. For example, a macro can make a dated copy in a Backups folder without changing the open workbook’s name or location; save a copy of the workbook before running any macro that could alter data, because VBA actions cannot be undone with Ctrl+Z.

What SaveCopyAs Does

Workbook.SaveCopyAs writes a copy of a workbook to the path you specify. Unlike SaveAs, it does not switch the open workbook to the new filename or location. You can therefore create an archive copy and continue working in the original workbook.

The copy reflects the workbook’s contents when the macro runs, including edits that are currently in memory. That makes SaveCopyAs useful before a risky operation, but it also means the copy is not necessarily identical to the last version previously saved to disk. If you specifically need a backup of the last saved state, save the workbook first, then make the copy. Decide which state you need before choosing the order.

The destination must be a valid full path, and its containing folder must exist or be created by your code. The copy normally keeps the workbook’s existing file type when you use the same filename extension. For example, a macro-enabled workbook should be copied with an .xlsm extension rather than renamed to .xlsx, which cannot preserve VBA macros.

A Dated Backup Macro for Windows

The following example creates a Backups subfolder beside the workbook, uses a timestamp in the copy’s name, and adds a number if another copy already has the same timestamp. It expects the workbook to have been saved at least once so that ThisWorkbook.Path is not empty. Put the code in a standard module in the workbook you want to back up.

Option Explicit

Public Sub CreateWorkbookBackup()
    Dim wb As Workbook
    Dim backupFolder As String
    Dim baseName As String
    Dim fileExtension As String
    Dim stamp As String
    Dim candidate As String
    Dim dotPosition As Long
    Dim suffix As Long

    On Error GoTo BackupError
    Set wb = ThisWorkbook

    ' A saved workbook has a folder path to use as the backup location.
    If Len(wb.Path) = 0 Then
        MsgBox "Save this workbook before creating a backup.", vbExclamation
        Exit Sub
    End If

    dotPosition = InStrRev(wb.Name, ".")
    If dotPosition = 0 Then
        MsgBox "The workbook filename has no extension.", vbExclamation
        Exit Sub
    End If

    baseName = Left$(wb.Name, dotPosition - 1)
    fileExtension = Mid$(wb.Name, dotPosition)
    backupFolder = wb.Path & Application.PathSeparator & "Backups"

    ' Create the one-level subfolder if it does not exist.
    If Len(Dir$(backupFolder, vbDirectory)) = 0 Then MkDir backupFolder

    stamp = Format$(Now, "yyyy-mm-dd_hh-nn-ss")
    candidate = backupFolder & Application.PathSeparator & _
                baseName & "_" & stamp & fileExtension

    ' Avoid replacing a copy made in the same second.
    suffix = 1
    Do While Len(Dir$(candidate)) > 0
        candidate = backupFolder & Application.PathSeparator & _
                    baseName & "_" & stamp & "_" & Format$(suffix, "00") & fileExtension
        suffix = suffix + 1
    Loop

    wb.SaveCopyAs candidate
    MsgBox "Backup created:" & vbCrLf & candidate, vbInformation
    Exit Sub

BackupError:
    MsgBox "The backup could not be created: " & Err.Description, vbCritical
End Sub

The code uses Application.PathSeparator rather than typing a backslash, and it reports errors such as a folder permission problem. The timestamp uses nn for minutes in VBA’s date format string; mm can mean month in a date context. The collision check prevents this macro from deliberately reusing a filename already present, but it does not remove old backups or limit how many accumulate.

How to Add and Run the Macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains VBA that must remain available. Choose File > Save As and select the macro-enabled format.
Open the Visual Basic Editor with Alt+F11 on Windows. On a Mac, use Option+F11 (you may also need the Fn key depending on the keyboard), or open the editor from the Developer tab.
In the editor, select the workbook project, then choose Insert > Module. Paste the code into the module and save the workbook.
Return to Excel and run CreateWorkbookBackup from Developer > Macros, select it, and choose Run. You can also run it from the Visual Basic Editor while the cursor is inside the procedure.

On Windows, the Developer tab may need to be enabled through File > Options > Customize Ribbon by checking Developer. Excel for Mac has different settings and may show the Developer tab under Excel > Preferences or Excel > Settings, depending on the release. Macro security controls whether VBA can run; only enable macros in workbooks you trust. The example uses VBA file-system operations, so check the destination and permissions when adapting it for a Mac or a cloud-synced location.

Choosing a Backup Location and Filename

The sample stores copies beside the original, which is convenient for a personal workbook on a local drive. It is not always the safest place: if the drive fails, both the original and its backup may be lost. For important files, keep another copy on a separate drive or in an approved cloud or network location, and make sure the intended destination is actually available before relying on it.

A useful filename identifies both the source and the time of the copy. The example produces names such as Sales_2026-11-02_14-30-05.xlsm. Consider these choices when you adapt the macro:

Backup frequency: Create a copy before a major edit, or run a scheduled process only if Excel is open and the workbook is in a known state.
Retention: Decide how long to keep dated copies. Without a cleanup policy, a frequently run macro can consume substantial storage.
Folder permissions: A protected network folder, full disk, offline drive, or unavailable mapped drive can make the save fail.
Privacy: A backup contains workbook data too. Store it where access is appropriate, especially if the workbook includes personal or confidential information.

For a fixed filename such as LatestBackup.xlsm, a later save may replace an earlier copy, depending on the operation and Excel’s overwrite handling. Dated names preserve multiple versions. If you use a fixed name, deliberately decide whether replacement is acceptable and test the workflow with disposable files first.

SaveCopyAs Compared with Save and SaveAs

These methods solve different problems. Workbook.Save saves changes to the workbook’s current file. Workbook.SaveAs saves under a new name or format and makes that file the workbook’s current saved identity. Workbook.SaveCopyAs writes a separate copy while leaving the open workbook associated with its original file.

For example, use Save when you want to commit current edits to the working file. Use SaveAs when you are intentionally creating a new working file—for example, turning a template into a new monthly workbook. Use SaveCopyAs when you want an additional snapshot but intend to keep editing the original. A copy is not a substitute for version history if you need automatic recovery, access controls, or a record of who changed what.

Another point of confusion is Excel’s AutoRecover feature. AutoRecover can help recover unsaved work after certain interruptions, but it is not the same as a deliberate, named backup you control. Likewise, cloud storage version history can be useful, but its availability and retention depend on the service, account, and organization settings. Do not assume either feature replaces a backup plan.

Common Problems and Fixes

The macro says to save the workbook first: A new workbook has no folder in ThisWorkbook.Path. Save it to a location before running this version, or change the code to use a chosen destination folder.
The backup folder cannot be created: The account may lack permission, or the parent location may not be reachable. Confirm the workbook’s folder is writable. The sample creates only one subfolder level; it will not build a chain of missing parent folders.
The copy is missing macros: Check that the source is macro-enabled and that the copy retains its .xlsm extension. Do not convert a VBA workbook to .xlsx if its code must be preserved.
The wrong workbook is copied: ThisWorkbook means the workbook containing the code. If you put this procedure in Personal.xlsb or an add-in, it refers to that file, not necessarily the workbook on screen. Put the procedure in the target workbook, or explicitly select and validate a target workbook.
The macro runs but the expected edits are absent: SaveCopyAs copies the workbook’s state at the time it is called. If your procedure changes data after making the copy, those later changes will not be in that backup. Place the call at the point that matches the snapshot you want.

VBA procedures cannot be reversed with Ctrl+Z. Before testing a backup macro or a larger automation routine, save a separate copy of the workbook and test on that copy. On Windows, Ctrl+S saves the current workbook; on Mac, use Command+S. Saving first is sensible, but it does not replace preserving a separate copy before a destructive change.

Safety and Backup Limits

A backup is useful only if it can be found and opened when needed. After the first run, inspect the Backups folder and open a copy to confirm that the filename, content, and file type are as intended. For a critical workbook, periodically test restoring a copy to a different folder rather than overwriting the live file. That checks both that the backup exists and that the restore process is understood.

Do not treat a second file in the same folder as protection against every risk. Accidental deletion, ransomware, hardware failure, or a synchronization mistake can affect nearby copies. A more resilient approach keeps multiple versions and at least one copy isolated from the everyday working location. Follow workplace rules for approved storage, retention, and confidential information. A macro can automate making a copy, but it cannot guarantee that the destination will remain available or that a backup is recoverable.

Frequently Asked Questions

Does SaveCopyAs change the workbook I have open?
It saves a separate copy and does not change the open workbook’s filename or make the copy the active saved identity. You can continue working in the original.

Does SaveCopyAs include unsaved changes?
The copy represents the workbook’s state when the method runs, including changes in memory. If you need the backup to match the last saved disk version, save the workbook before creating the copy.

Can VBA save a backup every time I save the workbook?
Yes, workbook event procedures can run code around a save, but an event-based design needs care to avoid repeated saves, unexpected prompts, and duplicate copies. Test it on a separate workbook and decide whether every save or only selected milestones should create an archive.

Will the macro work if the workbook has never been saved?
Not as written. It relies on ThisWorkbook.Path to make a folder beside the workbook. Save the workbook first, or change the code to use a known full destination path.

Can I use this backup macro in Excel for the web?
No. Excel for the web cannot run VBA macros. Open the workbook in the desktop Excel application to run VBA; the web version may provide other cloud-based file and version-history options depending on where the file is stored.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.