What Is VBA Save Copy As?
In VBA, “Save Copy As” is a method used to save a workbook copy with a new name or in a different location. This method is typically used when you want to save a copy of the current workbook without modifying the original file.
See the example below on how to use the VBA Save Copy As CSV function.

A subroutine is created to save a copy of the current workbook to another directory as a CSV file in excel. After running the subroutine, you can go to the directory to view the copied file.

Table of contents
Key Takeaways
- The Save Copy As function in VBA is used to save a copy of a workbook with a new name or in a different location.
- You typically achieve the same functionality using the SaveCopyAs method or the SaveAs method.
- The SaveCopyAs method is used to save a copy of the workbook in its current state without modifying the original file. It creates an exact duplicate of the workbook.
- The SaveAs method can also be used to save a copy of the workbook but with additional options such as specifying the file format, enabling read-only mode, and setting passwords.
- The Save Copy As function in VBA is a useful tool for creating duplicates of workbooks, allowing users to preserve the original while making changes to a separate copy.
Syntax
The syntax for using the VBA Save Copy As to SharePoint is as shown:
expression.SaveCopyAs (FileName)
Where,
expression: Variable representing any Workbook or Worksheet Objects.
How to Save Copy as XLSX using VBA?
Follow the steps below to learn how to save file copies as XLSX using the VBA Save Copy As function.
- Open a new Excel Workbook, then go to the “Developer” option on the Excel Toolbar.
Under the “Developer” section, select “Visual Basic” to open the Excel VBA (Visual Basic for Applications) Editor.
In the Editor, select “Insert” from the toolbar and select “Module” to open a new module to start coding. - Start by naming the subroutine to save a copy of the file in XLSX format.
- Set the current workbook in a workbook variable to copy the file and save the copy.
- Define a string variable to store the directory and the filename of the copied file.
- Save the current workbook in the directory defined earlier using the VBA Save Copy As function.
- Close the current workbook and do not save any changes made after the file has been copied.
Code:
Sub SaveCopyExample()
Dim originalWorkbook As Workbook
Set originalWorkbook = ThisWorkbook
Dim newFileName As String
newFileName = “D:\datasets\CopyOfWorkbook.xlsx”
originalWorkbook.SaveCopyAs newFileName
originalWorkbook.Close SaveChanges:=False
End Sub - Run the subroutine to copy the file and save this file in the D drive of your computer. You can change the directory and name as you prefer.
See some more ways how to use the VBA Save Copy As to SharePoint function.
Examples
Here, you can see some exciting and different ways to perform VBA Save Copy As different formats.
Example #1
You have a worksheet with values. You want to export it as a PDF file. It can be done by saving the workbook and using the ExportAsFixedFormat function to Save a copy of the workbook in a PDF file.

Step 1: Start with the process by naming the subroutine to do so. Choose a name that is easy to understand and informs the user of what it is trying to do.

Step 2: Define a string variable to store the directory and the PDF file name you want to store the copied values as.

Step 3: Use the ExportAsFixedFormat function and specify the type as PDF using the keyword “xlTypePDF”. Save this file in the location string defined in the previous step.

Note: You can use VBA Save Copy As to save the file in PDF format. But this will corrupt the file, and you will be unable to open it.

Code:
Sub SaveWorksheetsAsPDF()
Dim saveLocation As String
saveLocation = “D:\datasets\myPDF.pdf”
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
End Sub
Step 4: Run the above subroutine to save the worksheet data as a PDF File.


Example #2
Here, you will learn how to save a copy of the Excel file with a password triggered. Suppose you want to send a file with sensitive data through insecure connections. In that case, you can protect Excel files with a password lock, provided the receiver is aware of the password beforehand. You can save a copy of the Excel file to be sent with password protection using the VBA SaveAs function. Follow the steps below to learn how to code it in Excel VBA.
Step 1: Start the process of saving a password-protected copy of the Excel file by creating a subroutine to do so.

Step 2: Turn off any alerts while you’re opening the file. It makes the experience less tedious.

Step 3: Select the current Workbook using either ThisWorkbook or ActiveWorkbook and then call the VBA SaveAs function.

Since the file name is long, for easy readability, add an underscore ‘_’ after the function to continue it in the next line.
Step 4: Add the directory along with the file name to be saved. If you do not add any extension, it will save in a format similar to the current workbook, which is an XLSM file.

Define the password as one of the parameters in the SaveAs function and define the name of the password there.
Code:
Sub SaveFileWithPassword()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:=”D:\datasets\FileWithPassword”, Password:=”vbapro”
End Sub
Step 5: Run the subroutine to save a copy of the file with password protection. Then, go to the D drive after closing the workbook.

If you open the XLSM file, you get:

With that, you’ve successfully created a copy of the file with password protection.
Example #3
In this example, you want to save the given Excel file and want the user not to change or edit the worksheets in the workbook you sent. Instead of protecting the worksheet, you can save a copy where the author (you) can recommend that anyone opening the file not edit the values by recommending that this worksheet is only for reading.
You can do this by using VBA Save Copy As read-only. Using the VBA SaveAs function, you can edit the parameters to do the same.
Step 1: You can begin the process of VBA Save Copy As Read Only by creating a sub-procedure to do the same.

Step 2: Turn off any alerts while you’re opening the file after saving it. This makes the experience less tedious.

Step 3: Use the ActiveWorkbook to select the current workbook and then call the VBA SaveAs function.

To improve user readability of the code, an underscore ‘_’ is added after the function to continue it in the next line.
Step 4: Add the directory along with the file name. Specify the file type at the end of the file (be it .csv, .xlsx, or .xlsm) and what type of file you prefer to save it as.

After naming the directory, add another underscore, ‘_’, to add the following parameter.
Step 5: Set the “ReadOnlyRecommended” parameter as “True” to create a pop-up when the copied file is opened.

Code:
Sub recommend_read_only()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:=”D:\datasets\ReadOnlyFile.xlsx”, _
ReadOnlyRecommended:=True
End Sub
Step 6: Run the above sub-procedure to save the current workbook as a read-only recommended file.

If you open the file:

Important Things To Note
- Ensure that the filename you choose for the copy is meaningful and won’t overwrite existing files. Include a timestamp or other unique identifier if necessary.
- Choose a file format that is compatible with the applications or systems that will use the copied file.
- After saving the copy, consider verifying that the file was saved correctly and is accessible in the specified location.
- If the copied file contains sensitive information, ensure that appropriate security measures (e.g., password protection, encryption) are implemented to protect the data.
- Network issues, disk space limitations, or file permissions can prevent the saving process from completing successfully.
- If your VBA code creates temporary files or objects during the saving process, make sure to properly clean up these resources to avoid memory leaks or other performance issues.
Frequently Asked Questions (FAQs)
You can specify the file format for different Excel Files such as CSV, XLSX and XLSM files with VBA Save Copy as different format function parameters. You can’t directly save using the function in other file formats such as saving the Excel file as a PDF file.
Yes, you can. Write the name of your file after the Save Copy As function to save it in different names as shown below.
ActiveWorkbook.SaveCopyAs “C:\NewFolder\YourFileName.xlsx”
No, VBA Save Copy As does not overwrite the existing file. Any changes made to the original file will not be shown in the file copy.
Yes, once the copying is successful, you can print a Message Box confirming the process or, you can go to the file directory to check if your Excel File has been saved successfully.
Download Template
This article must help us understand the VBA Save Copy As formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to VBA Save Copy As in Excel. Here we explain how to create & use save copy as method, along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply