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."

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.

• 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.

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

Now, use the Save As method.

Code:

Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs End Sub

Now, identify where you want to save.

Code:

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

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

Now, mention the file format as "xlWorkbook."

Code:

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

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

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

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.