VBA Save As

Last Updated :

21 Aug, 2024

Blog Author :

N/A

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.

The VBA "Save As" function is important for saving files with different names or formats, and VBA offers many other useful functions. Learning VBA allows users to automate tasks and create custom functions in applications like Microsoft Excel and Word. Those looking to further enhance their data management skills and advance their careers can explore this Microsoft Office VBA Advanced Course.

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 SubVBA Save As Example 1

Now, use the Save As method.

Code:

Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs End SubVBA Save As Example 1-1

Now, identify where you want to save.

Code:

Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs "D:Articles2019 End SubExample 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 SubExample 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 SubExample 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 SubVBA 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 SubVBA 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: -