[Excel VBA] Differences Between the Three Methods to Save a Workbook (Save/SaveAs/SaveCopyAs) and How to Use Them

2025年12月30日

When you finish processing a workbook in VBA, the “Save” operation is always necessary at the end. However, VBA has three similar save methods: .Save, .SaveAs, and .SaveCopyAs. If you do not understand the differences correctly, it may lead to accidents such as unintentionally overwriting important files.

This article clearly explains the role of each of the three save methods and when to use them.

1. Overwrite Save (.Save)

.Save is the simplest method. It performs a simple overwrite save on the currently open workbook. This is the same operation as manually pressing Ctrl + S or clicking the floppy disk icon.

Code Example

Sub OverwriteSave()
    Dim targetBook As Workbook
    Set targetBook = ActiveWorkbook
    
    ' Check if path exists because it errors if the workbook has never been saved
    If targetBook.Path <> "" Then
        targetBook.Save
        MsgBox "Overwrote and saved workbook '" & targetBook.Name & "'."
    Else
        MsgBox "This workbook has not been saved yet. Please use 'Save As'.", vbExclamation
    End If
End Sub

Point: This method is valid only for workbooks that already exist as files. If you run this on a newly created, unsaved workbook (e.g., “Book1”), an error will occur.

2. Save As New Name (.SaveAs)

.SaveAs is a method to save the workbook with a new name, in a different location, or in a different format. It corresponds to the manual “Save As” operation.

Code Example

Sub SaveAsNewName()
    Dim targetBook As Workbook
    Set targetBook = ActiveWorkbook
    
    Dim newFilePath As String
    ' Create a path to save with a different name in the same folder as this Excel file
    newFilePath = ThisWorkbook.Path & "\Report_FinalVersion.xlsx"
    
    ' Save As
    targetBook.SaveAs Filename:=newFilePath
    
    ' After SaveAs, VBA's operation target switches to the new workbook
    MsgBox "Saved as '" & ActiveWorkbook.Name & "'."
End Sub

Most Important Point: When you execute .SaveAs, the target of subsequent VBA operations switches to the newly saved workbook. The original file is closed (unless it was modified), and VBA control moves to the new file.

3. Save a Copy (.SaveCopyAs)

.SaveCopyAs saves a copy of the current workbook with a different name. This is a useful method for creating backups or snapshots.

Code Example

Sub SaveACopy()
    Dim targetBook As Workbook
    Set targetBook = ActiveWorkbook
    
    ' Create a backup file path by adding the date to the original filename
    Dim backupFilePath As String
    backupFilePath = Replace(targetBook.FullName, ".xls", "_" & Format(Date, "yyyymmdd") & ".xls")

    ' Save a copy
    targetBook.SaveCopyAs Filename:=backupFilePath
    
    ' Even after SaveCopyAs, the operation target remains the original workbook
    MsgBox "Saved a backup copy to '" & backupFilePath & "'." & vbCrLf & _
           "Continuing to edit '" & ActiveWorkbook.Name & "'."
End Sub

Most Important Point: The decisive difference from .SaveAs is that even if you execute .SaveCopyAs, the VBA operation target remains the original workbook. This is ideal when you want to save the current state as a separate file while continuing to work on the original workbook.

Summary: Distinguishing the Three Methods

It is important to understand the differences between the three methods and use them correctly according to your purpose.

Method	Operation Overview	Target After Execution	Main Use
.Save	Overwrite existing file	Unchanged (Original)	Saving progress during processing.
.SaveAs	Save with a new name	Switches to new workbook	Saving the result as a separate final version file.
.SaveCopyAs	Save a copy with a new name	Unchanged (Original)	Creating backups or snapshots during processing.