VBA Save As

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Excel VBA Save As

VBA Save As is the method used to save the Excel file to a specific location. To save the workbook using VBA code, we use the object Workbook with the SaveAs function.

After all the hard work we have done in the workbook, we save it. It is painful to lose the data we have worked on. We have two kinds of saves in Excel or any file. One is "Save," and another is "Save As." Ctrl + S is the popular shortcut key as the Ctrl + C and Ctrl + V around the globe. But we are not that familiar with the concept of "Save As." The worksheet shortcut to Save As the file in the regular worksheet is the F12 key. In VBA, too, we can save the file as "Save As."

Table of contents
VBA-Save-As

What does VBA Save As Function do?

It is a general task of saving work in the automation process. Finally, after all the work, we wish to save the file.

Workbooks("Sales 2019.xlsx").Save

This code reads a Workbook named "Sales 2019.xlsx" to save.

Similarly, replicating the workbook we are working on can be created using the "Save As" method.

SaveAs Syntax
  • File Name: What is the file name you wish to give? One should combine this with the file folder path.
  • File Format: What should be the format for the file you are saving?
  • Password: Would you like to provide a password for the saving file?
  • Write Res Password: Mention the reserved password for the workbook.

We think these parameters are enough to understand the Save As method.

How to use Save As Function?

Example #1

We save a workbook, right? so it is important to mention the workbook name and its extension to use the SaveAs method. So, mention the workbook you are saving.

Code:

Sub SaveAs_Example1()

  Workbooks("Sales 2019.xlsx").

End Sub
VBA Save As Example 1

Now, use the Save As method.

Code:

Sub SaveAs_Example1()

Workbooks("Sales 2019.xlsx").SaveAs

End Sub
VBA Save As Example 1-1

Now, identify where you want to save.

Code:

Sub SaveAs_Example1()

Workbooks("Sales 2019.xlsx").SaveAs "D:Articles2019

End Sub
Example 1-2

Now, put backslash and enter the file name as per your wish with a file extension.

Code:

Sub SaveAs_Example1()

Workbooks("Sales 2019.xlsx").SaveAs "D:Articles2019My File.xlsx"

End Sub
Example 1-3

Now, mention the file format as "xlWorkbook."

Code:

Sub SaveAs_Example1()

Workbooks("Sales 2019.xlsx").SaveAs "D:Articles2019My File.xlsx", FileFormat:=xlWorkbook

End Sub
Example 1-4

It will save the file in D drive > Folder Name (Articles) > Sub Folder Name (2019).

Example #2

Save All the Opened Workbooks

Assume you are working with 10 workbooks on your computer. You want to create a backup of these workbooks by saving them on the computer as one of the copies. When you want to work with more than one workbook, it is necessary to use the loops.

The below code will help you save all the workbooks as a copy.

Code:

Sub SaveAs_Example2()

  Dim Wb As Workbook

  For Each Wb In Workbooks
  ActiveWorkbook.SaveAs "D:Articles2019" & ActiveWorkbook.Name & ".xlsx"
  'Change the file path
  Next Wb

End Sub
VBA Save As Example 2

You can use this code if you wish to select your folder path.

Code:

Sub SaveAs_Example3()

 Dim FilePath As String

FilePath = Application.GetSaveAsFilename

ActiveWorkbook.SaveAs Filename:=FilePath & ".xlsx", FileFormat:=xlOpenXMLWorkbook

End Sub
VBA Save As Example 3

When you run this code using the F5 key or manually, it will ask you to select the destination folder path. Select and click on "OK." It will save the file.