Skip to main content

Workbook.SaveCopyAs method

Table of contents
  1. Syntax
  2. Parameters
  3. Example

Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.


expression.SaveCopyAs (FileName)

expression A variable that represents a Workbook object.


Name Required/Optional Data type Description
FileName Required Variant Specifies the file name for the copy.


The following example saves a copy of the active workbook.

ActiveWorkbook.SaveCopyAs "D:\TEMP\XXXX.XLSX"

The following example saves a copy of the active workbook with specify file name.

Sub BackupWorkbook()
    Dim MyFileName As String, MyFileExt As String, MyNewFileName As String
    MyFileName = ThisWorkbook.Name
    MyFileExt = Right(MyFileName, Len(MyFileName) - InStrRev(MyFileName, "."))
    MyNewFileName = Left(MyFileName, InStrRev(MyFileName, ".") - 1) & "_" & Format(Date, "d mmm yyyy")
    ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & "\" & MyNewFileName & "." & MyFileExt
End Sub

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>