Excel VBA: Save a Workbook as a New File with SaveAs

To save an Excel workbook as a new file with VBA, use the workbook’s SaveAs method and supply a new filename and, when needed, a file format. Use SaveCopyAs instead if you want to create a separate copy while keeping the workbook open under its original name.

SaveAs and SaveCopyAs do different things

The key choice is whether the workbook you are working in should become the new file. Workbook.SaveAs saves the workbook under another name or in another location and changes the open workbook’s identity. After it succeeds, subsequent saves normally go to the new path. If a macro saves Quarterly.xlsx as Quarterly-final.xlsx, the open workbook is now the final file.

Workbook.SaveCopyAs writes a copy but leaves the open workbook associated with its existing filename and location. That is useful for creating a dated backup or a handoff copy without redirecting later saves. It saves the workbook in its current file format; it does not offer a FileFormat argument for converting the copy.

Choose SaveAs when the new file should become the workbook you continue editing.
Choose SaveCopyAs when you need a duplicate and want to keep working in the original.
Choose the format deliberately: the filename extension and the actual workbook format should match.

A basic SaveAs macro

This Windows desktop example saves the active workbook beside its current file, adds a timestamp to the name, and creates an Excel macro-enabled workbook. The timestamp reduces the chance of accidentally choosing a name already in use. The workbook must already have a saved location so the macro can build a destination path.

Sub SaveActiveWorkbookAsNewFile()
    Dim target As Workbook
    Dim dotPos As Long
    Dim baseName As String
    Dim newPath As String

    On Error GoTo SaveFailed
    Set target = ActiveWorkbook

    ' Save the source workbook once before using its folder and name.
    If Len(target.Path) = 0 Then
        MsgBox "Save the workbook once before running this macro."
        Exit Sub
    End If

    dotPos = InStrRev(target.Name, ".")
    If dotPos = 0 Then
        MsgBox "The workbook name has no file extension."
        Exit Sub
    End If

    baseName = Left$(target.Name, dotPos - 1)
    newPath = target.Path & Application.PathSeparator & _
              baseName & "_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsm"

    ' SaveAs changes the open workbook's name and file format.
    target.SaveAs Filename:=newPath, _
                  FileFormat:=xlOpenXMLWorkbookMacroEnabled
    MsgBox "Saved new file: " & newPath
    Exit Sub

SaveFailed:
    MsgBox "The workbook could not be saved: " & Err.Description
End Sub

Before running any macro, save a separate backup of important work. VBA actions cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert the code into a standard module, and run the procedure from the editor or the Macros dialog. Mac keyboard shortcuts differ, and the editor’s available shortcuts depend on the keyboard and Excel release; the VBA editor can also be opened from the Developer tab. Enable the Developer tab through File > Options > Customize Ribbon if it is not shown.

Choose the workbook the macro should save

The example uses ActiveWorkbook, which means the workbook currently active in Excel. That is convenient when the macro is launched for the file on screen, but it can be risky if another workbook becomes active or the macro is stored elsewhere. If the code is stored inside the workbook it should save, ThisWorkbook refers specifically to the workbook containing that code. These references can point to different files.

For example, a macro stored in the Personal Macro Workbook should generally not assume that ThisWorkbook is the report being processed: it refers to the Personal Macro Workbook. Conversely, an automation routine that must always save its own file should not rely on whichever workbook happens to be active. Set a clearly named workbook variable to the intended target before calling SaveAs, and check the resulting path when the destination matters.

The example derives the new name from the existing workbook name and saves into the same folder. If you want a fixed folder, build the destination from a path you control and verify that the folder exists first. A missing folder, insufficient write permission, a read-only destination, or a disconnected network location can cause the save to fail.

Match the file format to the extension

For a macro-enabled Excel workbook, use the .xlsm extension with xlOpenXMLWorkbookMacroEnabled, as in the example. For a standard workbook without VBA, the usual pair is .xlsx and xlOpenXMLWorkbook. A binary workbook uses .xlsb and xlExcel12. Legacy Excel workbooks use different formats, so do not change just the extension and assume the file has been converted.

Saving a workbook that contains VBA as .xlsx is a common source of confusion. The XLSX format cannot store VBA project code. Excel may warn that macros will be removed if you continue; choosing to proceed means the resulting file will not retain that VBA project. Keep macro-bearing files as .xlsm or another VBA-capable format when the code must remain available. If your purpose is a clean, macro-free deliverable, make that conversion intentionally and preserve a macro-enabled original.

Saving as a different format can also affect features beyond macros. Some formats do not support all workbook features, and Excel can display a compatibility warning. Review the warning rather than suppressing it blindly. If you are saving a CSV, for example, the output is not a complete multi-sheet workbook: CSV stores text data for a single sheet and does not preserve workbook formatting, formulas as formulas, or VBA.

Create a copy without changing the open workbook

When the requirement is “make a backup, then keep editing the original,” use SaveCopyAs. The following version copies the active workbook into its existing folder, keeps its extension, and appends a timestamp. Unlike the earlier example, it does not convert the file format or switch the open workbook to the copied filename.

Sub MakeTimestampedCopy()
    Dim target As Workbook
    Dim dotPos As Long
    Dim baseName As String
    Dim extension As String
    Dim copyPath As String

    On Error GoTo CopyFailed
    Set target = ActiveWorkbook

    ' A saved source provides a folder and a filename to reuse.
    If Len(target.Path) = 0 Then
        MsgBox "Save the workbook once before making a copy."
        Exit Sub
    End If

    dotPos = InStrRev(target.Name, ".")
    If dotPos = 0 Then
        MsgBox "The workbook name has no file extension."
        Exit Sub
    End If

    baseName = Left$(target.Name, dotPos - 1)
    extension = Mid$(target.Name, dotPos)
    copyPath = target.Path & Application.PathSeparator & _
               baseName & "_copy_" & Format(Now, "yyyymmdd_hhnnss") & extension

    ' This writes a copy without changing the open workbook's name.
    target.SaveCopyAs copyPath
    MsgBox "Created copy: " & copyPath
    Exit Sub

CopyFailed:
    MsgBox "The copy could not be created: " & Err.Description
End Sub

A copy is not automatically a backup strategy by itself. A copy saved beside the original may be lost if the drive or folder is damaged, and timestamped copies can accumulate. For important files, choose a location with appropriate access controls and a separate backup or version-history process. Also check that the copy contains the intended latest edits; the macro saves the workbook’s current in-memory contents.

Overwrite prompts and common save errors

If the destination filename already exists, Excel may ask whether to replace it. That prompt is protective: replacing a file can destroy the previous contents. A timestamp makes collisions less likely, but not impossible if the macro runs more than once within the same second. You can add a counter, a more precise unique identifier, or a check for an existing destination and choose another name.

Some macros set Application.DisplayAlerts = False to suppress prompts. This can allow an existing file to be overwritten without confirmation, so it should not be added casually. If a macro changes this application-wide setting, it should restore the prior value even when an error occurs. For routine file creation, allowing Excel’s normal prompt is often safer than hiding it.

Other common causes of failure include a folder that does not exist, a file open elsewhere with restrictive sharing, read-only permissions, an invalid filename character, or a path that is too long for the environment. Windows filenames cannot contain characters such as a colon or question mark; timestamps should use safe characters such as digits, underscores, and hyphens. An error handler can report the failure, but it cannot make an inaccessible destination writable. Check the path and permissions first.

Windows, Mac, and Excel for the web

The VBA examples are intended for desktop Excel. On Windows, Application.PathSeparator supplies the path separator used when joining the folder and filename. Using it is more portable than typing a backslash into every path expression. On Mac, desktop Excel supports VBA, but file locations, permissions, and some VBA features can differ from Windows. Test a save routine with a copy in the intended Mac folder before using it on important files.

Excel for the web does not run VBA macros. A workbook containing macros can be opened in the web app, but the VBA procedure must be run in desktop Excel. If the file is stored in OneDrive or SharePoint, use a location that desktop Excel can access and confirm that syncing has completed before relying on the saved copy. Cloud sharing and version history can offer alternatives for retaining earlier versions, but they do not make a web browser execute VBA.

Frequently Asked Questions

Does VBA SaveAs overwrite the original workbook? Not merely because you call SaveAs. If you provide a different destination name, Excel saves under that name and the open workbook becomes associated with the new file. If the destination already exists, Excel may ask whether to replace it; verify the path and prompt before approving.

How do I save a copy and keep the original open? Call SaveCopyAs on the intended workbook and provide a different full path. It creates a copy while the original workbook remains open under its current name. It preserves the workbook’s current format rather than converting it.

Why does my saved workbook lose its macros? The destination may have been saved in a format that does not support VBA, particularly .xlsx. Use an appropriate macro-enabled format such as .xlsm when you need the code to remain in the file, and check Excel’s format warnings.

Can I use SaveAs to save directly to OneDrive or SharePoint? Yes, if desktop Excel can access the destination and the account has permission, but the precise path and synchronization behavior depend on how the location is configured. Use a valid accessible path, handle save errors, and confirm the file has synced if another person or device needs it.

Can I undo a VBA save with Ctrl+Z? No. Excel does not provide the normal undo history for a macro’s file-saving action, and a macro can make other changes that cannot be reversed with Ctrl+Z. Save a separate backup before running code that changes or replaces important files.

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.