In Excel VBA, use Workbook.SaveCopyAs to save a copy of an open workbook without changing the name or location of the workbook you are working in. For example, ThisWorkbook.SaveCopyAs "C:\Reports\Report-copy.xlsm" writes a copy to that path; use a filename extension that matches the workbook’s existing format.
What SaveCopyAs Does
SaveCopyAs creates a file containing a copy of a workbook’s current contents. The original workbook remains open under its existing name and location. This makes the method useful for backups, dated snapshots, or creating a deliverable while continuing to work in the source workbook.
The method belongs to a Workbook object, so the workbook reference matters. ThisWorkbook means the workbook that contains the running VBA code. ActiveWorkbook means the workbook currently in front of the user, which may be different—for example, if a macro stored in Personal.xlsb or an add-in is running. Use the reference that identifies the intended source.
A copy is not the same as a new, empty workbook, nor does SaveCopyAs switch the open workbook to the copy. The code does not close the source or make the new file the active workbook. If the intention is to continue editing under a new filename, use Save As instead.
A Safe Timestamped VBA Example
This example saves a timestamped copy beside the workbook that contains the macro. It stops if the source workbook has never been saved, preserves the source extension, and checks whether the generated destination already exists.
Sub SaveTimestampedCopy()
Dim sourceBook As Workbook
Dim copyPath As String
Dim extension As String
Dim dotPosition As Long
' Use the workbook that contains this code.
Set sourceBook = ThisWorkbook
' A saved source provides a folder and a known file extension.
If Len(sourceBook.Path) = 0 Then
MsgBox "Save this workbook before creating a copy.", vbExclamation
Exit Sub
End If
dotPosition = InStrRev(sourceBook.Name, ".")
If dotPosition = 0 Then
MsgBox "The workbook name has no file extension.", vbExclamation
Exit Sub
End If
extension = Mid$(sourceBook.Name, dotPosition)
' Application.PathSeparator works with the current platform's path style.
copyPath = sourceBook.Path & Application.PathSeparator & _
"Backup_" & Format$(Now, "yyyymmdd_hhnnss") & extension
' Avoid intentionally writing over a copy with the same timestamp.
If Len(Dir$(copyPath)) > 0 Then
MsgBox "A file already exists at: " & copyPath, vbExclamation
Exit Sub
End If
sourceBook.SaveCopyAs Filename:=copyPath
MsgBox "Copy saved to:" & vbCrLf & copyPath, vbInformation
End Sub
The timestamp format produces a name such as Backup_20261103_142530.xlsm. The date and time make it easier to distinguish snapshots; the seconds reduce the chance that two runs generate the same name. For a workbook with a different extension, the macro carries that extension through rather than converting the file.
Before running any macro, save a separate copy of the workbook. VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Code > Visual Basic. The Developer tab may need to be enabled in Excel Options. Excel for the web cannot run VBA macros; use the desktop app. Excel for Mac supports VBA, but some Windows-specific VBA features and paths differ.
Choosing a Filename and Folder
The argument to SaveCopyAs is a full destination filename, not just a folder. A path such as C:\Reports\April-copy.xlsm identifies both where the copy should go and what it should be called. If you provide only a filename, Excel may use the current folder, which is not always obvious; an explicit full path is easier to maintain.
The sample uses sourceBook.Path, so its copy goes into the source workbook’s folder. That is convenient for a local folder or an accessible network folder, but it may be unsuitable if you want backups kept elsewhere. To choose a different destination, replace the path-building expression with a full path to a folder that exists and that the user can write to. VBA does not create missing parent folders automatically.
- Use a stable folder for routine backups, and check that it is available before relying on the macro.
- Use
Application.PathSeparatorwhen joining a folder and filename instead of assuming a slash style. - Do not build a path from a workbook that has not been saved without first choosing a valid destination; its
Pathis empty. - Keep filenames free of characters Windows does not allow, such as
:,*, and?.
If the workbook is in a OneDrive or SharePoint-synced folder, the local synchronization client and permissions affect whether the copy appears and syncs as expected. A cloud URL is not always interchangeable with a normal local folder path in VBA. For a dependable automated process, verify that the path returned by Excel is a location where the macro can write files.
File Formats and Extensions
Unlike Workbook.SaveAs, SaveCopyAs does not offer a FileFormat argument for converting the workbook. It saves a copy in the workbook’s existing file format. Therefore, retain a matching extension: for example, a macro-enabled workbook should normally keep .xlsm, while an Excel binary workbook should keep .xlsb.
Changing only the filename suffix is not a reliable way to convert a workbook. Renaming an .xlsm copy to .xlsx does not perform a proper Save As conversion and can cause format warnings or confusion. If a different format is required, use SaveAs with the appropriate format setting on a workbook you intend to convert, and understand that this changes the saved identity of that workbook. For a copy in another format, a separate workbook workflow is needed.
For a macro to remain available when a user opens the copy, the destination format must support VBA. The standard macro-enabled workbook format is .xlsm. An .xlsx workbook does not store VBA project code. Save the macro-containing source in a macro-enabled format before relying on it as the basis for future macro runs.
SaveCopyAs Compared with SaveAs
The names are similar, but they solve different tasks. SaveAs saves the workbook under a new filename or format and makes that file the workbook’s new saved identity. SaveCopyAs writes a separate file while leaving the workbook currently open as it was. It is commonly the better fit for an automatic backup made during a work session.
- Use SaveCopyAs when you want a snapshot or backup and then want to keep working in the original workbook.
- Use SaveAs when you want the open workbook to become a newly named file, or when you need to choose a different file format.
- Use Save when you simply want to write current changes to the workbook’s existing file.
A frequent misconception is that SaveCopyAs both creates a backup and switches the user to it. It does not. If a workflow requires editing the new file after creating it, the macro must deliberately open the copy with a separate operation, such as Workbooks.Open. That step should be considered carefully: it can leave two similar workbooks open, making it easy to edit the wrong one.
Overwrite Behavior and Common Errors
Choose a naming policy rather than assuming a destination can safely be reused. If a file already exists, Excel may display an overwrite prompt depending on the context and application settings. Suppressing alerts can make a macro run unattended, but it can also hide a destructive overwrite. The example avoids intentionally reusing an existing timestamped filename instead of turning off alerts.
Common causes of failure include an invalid path, a folder that does not exist, insufficient write permission, a file locked by another process, or a network location that is unavailable. A source workbook that has not been saved also has no meaningful source folder for the sample’s path construction. If an error occurs, check the complete destination path and permissions before changing the macro’s error handling.
For production automation, consider handling errors and reporting the destination clearly. Avoid a broad “ignore all errors” handler: it can make a failed backup look successful. A useful process distinguishes a completed save from a failure, and does not delete or replace older backups until the new copy has been confirmed. If backups matter operationally, also consider where they are stored and how long they are retained.
When a Copy Is Not a Complete Backup
SaveCopyAs creates another workbook file; it is not a version-control system or a full computer backup. It does not by itself maintain a history policy, protect the file from later deletion, or guarantee that a copy stored in the same folder will survive a drive failure. For important workbooks, store copies on a separate drive or managed backup service as appropriate, and periodically confirm that a saved copy can be opened.
Also distinguish the workbook file from external dependencies. A workbook may rely on linked files, data connections, add-ins, templates, or permissions that are not contained in the copied workbook. Copying the workbook does not necessarily copy those resources or make external links point to new locations. If the goal is to hand off a self-contained report, inspect its links and connections separately.
Macros may also have effects beyond the workbook itself. SaveCopyAs saves the workbook file, but it does not reverse changes a macro has made to other files, databases, or external systems. The backup is a snapshot of the workbook, not a rollback mechanism for every action the automation performed.
Frequently Asked Questions
Does SaveCopyAs change the name of the open workbook?
No. It creates a file at the destination path and leaves the source workbook open under its current name and location. Use SaveAs if the open workbook should adopt the new name.
Can SaveCopyAs create a copy in another folder?
Yes. Provide the full path and filename for the destination, and make sure the destination folder exists and is writable. The method does not create missing folders for you.
Can I use SaveCopyAs to convert XLSM to XLSX?
No. SaveCopyAs has no file-format conversion argument. Keep the copy’s extension consistent with its existing format; use SaveAs for a format conversion, taking care because SaveAs changes the workbook’s saved identity.
Does SaveCopyAs include changes I have not saved yet?
It saves a copy of the workbook’s current in-memory contents, so changes made in the open workbook can be present in the copy. Still, save the source first when you need a clear, recoverable starting point, and verify important backups.
Can Excel for the web run this VBA macro?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel to run the macro; Excel for Mac also has VBA support, though platform-specific code may need adjustment.
Can I undo a macro that used SaveCopyAs?
Ctrl+Z does not undo VBA macro actions. Keep a separate backup before running macros, and treat a newly created copy as a file to inspect or remove manually if it is not needed.
Related
- Excel Job List Template: Track Applications and Follow-Ups
- Excel To-Do List Templates: How to Choose and Use One
- Excel Meal Prep Template: Plan Meals, Shop Smarter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Enum: How It Works and When to Use ItAn Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such…
- VBA: Open a UserForm When a Command Button Is ClickedTo open an Excel VBA UserForm when a worksheet button is clicked, assign a public macro to a…
- Insert a Table in Excel Using VBA: Code and TipsIn Excel for Microsoft 365 on Windows, create a table with VBA by specifying the range that contains…
- How to Find a VBA Project Password in Excel SafelyExcel cannot display or reveal a forgotten VBA project password. To regain legitimate access, check your password manager…
- VBA Sort: How to Sort Excel Data Safely with a VBA MacroVBA does not have a standalone Sort function for worksheet data; in Excel macros, sorting is usually done…
- Excel UserForm Controls: A Practical Guide to Types and UseExcel UserForm controls are the buttons, text boxes, lists, and other interface elements you add to a VBA…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.