Excel VBA: Save Workbook in Specific Folder (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show you 4 methods of using Excel VBA to save Workbook in a specific folder. Time is very valuable, we can save a lot of time by skipping mundane tasks using Excel VBA. Moreover, We have taken a dataset consisting of 3 columns: “Name”, “Car Model”, and “Car Maker”. This dataset represents 6 employees’ car information for a particular company.

Table of Contents hide

Download Practice Workbook

4 Examples to Save Workbook in Specific Folder Using VBA in Excel

1. Excel VBA to Save Active Workbook in Specific Folder

2. Save Workbook in Specific Folder by Utilizing Save As Dialog Box

3. Save Specific Workbook in Specific Folder by Using Excel VBA

4. Use of Excel VBA to Save All Opened Workbooks in Specific Folder

Conclusion

Download Practice Workbook

Save Workbook in Specific Folder.xlsm

4 Examples to Save Workbook in Specific Folder Using VBA in Excel

1. Excel VBA to Save Active Workbook in Specific Folder

For the first method, we will save the Active Workbook in a specific folder using VBA Macro. We will bring up the VBA Module window, type our code and then execute the code to achieve our goal. Without further ado, let us jump into the steps by steps procedure.

Steps:

Before, typing our code we need to bring up the VBA Module. To do that –

First, from the Developer tab >>> select Visual Basic.

Alternatively, you can press ALT + F11 to do this. The “Microsoft Visual Basic for Application” will appear after this.

Secondly, from Insert >>> select Module.

Here, we will type our code.

Thirdly, type the following code inside the Module.

Sub Save_Specific_Folder()
ActiveWorkbook.SaveAs _
Filename:="C:\Users\Rafi\OneDrive\Desktop\Exceldemy\Save in Specific Folder.xlsm"
End Sub

VBA Code Breakdown

First, we are calling our Sub Procedure Save_Specific_Folder.
Then, we are referring to our current Workbook as ActiveWorkbook.
After that, using the SaveAs method we are saving our file in a location.
Finally, we have given a Filename to our Workbook, which is “Save in Specific Folder.xlsm”.

Now, we will execute our code.

First, Save this Module.
Secondly, click inside our code.
Finally, press the Run button.

If we go to our defined folder location, we will see our file is there. Thus, we have successfully saved the Workbook in a specific folder using Excel VBA.

2. Save Workbook in Specific Folder by Utilizing Save As Dialog Box

For the second method, we will use the File Save As dialog box inside our VBA code to save Workbook in a specific folder.

Steps:

First, as shown in method 1, bring up the VBA Module.
Secondly, type this code inside that Module.

Sub Save_Specific_Folder_Selection()
Dim x1 As String
Dim x2 As Variant

x1 = "VBA Save Workbook Specific Folder"
x2 = Application.GetSaveAsFilename(InitialFileName:=x1, _
FileFilter:="Excel Files (*.xlsm), *.xlsm")
    If x2 <> False Then
    ActiveWorkbook.SaveAs x2
    End If
End Sub

VBA Code Breakdown

First, we are calling our Sub Procedure Save_Specific_Folder_Selection.
After that, we have defined the variable type.
Then, we are setting the initial file name as “VBA Save Workbook Specific Folder“. This will be our default filename inside the Save As dialog box.
After that, we set the file type as xlsm.
Finally, if we change the filename, it will execute the VBA If Statement to change the filename.

Thirdly, as shown in method 1, execute the code.

Then, we’ll see the Save As dialog box.

Then, type a different filename if you want, this is optional.
After that, press Save.

Thus, Excel VBA to save Workbook in a specific folder is complete.

Similar Readings

3. Save Specific Workbook in Specific Folder by Using Excel VBA

For the third method, we are going to save a specific Workbook in a folder using Excel VBA. If we hover over the Excel Application icon on the Taskbar, we can see two files are opened. Here, we will save the first Workbook from the left side using a VBA code.

Steps:

First, as shown in method 1, bring up the VBA Module.
Secondly, type this code inside that Module.

Sub Save_Specific_Workbook_in_Specific_Folder()

Dim z As Workbook
Set z = Workbooks("Save Workbook Macro.xlsm")
z.SaveAs "C:\Users\Rafi\OneDrive\Desktop\Exceldemy\" & z.Name

End Sub

VBA Code Breakdown

First, we are calling our Sub Procedure Save_Specific_Workbook_in_Specific_Folder.
After that, we have defined the variable type.
Then, we are setting the Workbook to be saved as “Save Workbook Macro.xlsm“.
After that, using the SaveAs method we are saving our file in a location.
Finally, we keep the filename the same as the Workbook using the Name property.

Thirdly, as shown in method 1, execute the code.

After this, it will save our Workbook inside our defined folder location. Thus, we have shown you yet another method of saving Workbook in a specific folder using VBA.

4. Use of Excel VBA to Save All Opened Workbooks in Specific Folder

For the last method, we will save all the opened Workbooks in a specific folder. This time, we have the same two Workbooks as in method 3, however, this time, we’ll save both Workbooks inside a defined folder. Here, we will use the For Next Loop to go through our Workbooks.

Steps:

First, as shown in method 1, bring up the VBA Module.
Secondly, type this code inside that Module.

Sub Save_Specific_Folder_All_Workbook()

For Each Workbook In Workbooks
Workbook.SaveAs Filename:= _
        "C:\Users\Rafi\OneDrive\Desktop\Exceldemy\" & Workbook.Name, _
        FileFormat:=52
Next

End Sub

VBA Code Breakdown

First, we are calling our Sub Procedure Save_Specific_Folder_All_Workbook.
Then, we are using a For Next Loop to cycle through all Workbooks.
After that, using the SaveAs method we are saving our file in a location.
Finally, we keep the filename the same as the Workbook using the Name property.

Thirdly, as shown in method 1, execute the code.

Therefore, it will save the two Workbooks in our specified folder. In conclusion, we have shown 4 different Excel VBA Macros to save Workbook in a specific folder.

Conclusion

We have shown you 4 quick-and-easy to understand Excel VBA to save Workbook in a specific folder. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!