VBA Save As

Excel VBA Save As

VBA Save As method is 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, isn’t 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 one is “Save As.” Ctrl + S is the popular shortcut key as the Ctrl + C & Ctrl + V around the globe. But it is not that familiar with the concept “Save As.” In regular worksheet shortcut key to Save As the file 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. After all the work, we wish to save the file.

Workbooks("Sales 2019.xlsx").Save

This code read Workbook named as “Sales 2019.xlsx” to save.

Similarly, to replicate the workbook we are working on can be created by using the “Save As” method.

SaveAs Syntax
  • File Name: What is the name of the file you wish to give. This should be combined with the file folder path.
  • File Format: What is 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 reserved password for the workbook.

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

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 workbook right, so it is important to mention the workbook name and its extension to use the Save As 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:\Articles\2019

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:\Articles\2019\My File.xlsx"

End Sub
Example 1-3

Now mention the file format as “xlWorkbok.”

Code:

Sub SaveAs_Example1()

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

End Sub
Example 1-4

Ok, we are done. 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 back up of these workbooks by saving them on the computer as one of the copy. 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:\Articles\2019\" & ActiveWorkbook.Name & ".xlsx"
  'Change the file path
  Next Wb

End Sub
VBA Save As Example 2

If you wish to select your own folder path, then you can use this code.

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

As soon as 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 has been a guide to VBA Save As. Here we learn how to use save as a method to save an excel workbook along with examples & downloadable templates. Below are some useful articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>