FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Save As

Updated on June 12, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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

VBA-Save-As

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Save As (wallstreetmojo.com)

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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to use Save As Function?

You can download this VBA Save As Excel Template here – VBA Save As Excel Template

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.

Recommended Articles

This article has been a guide to VBA Save As. Here, we learn how to use Save As to save an Excel workbook, examples, and downloadable templates. Below are some useful articles related to VBA: –