How to a Save Workbook in a Specific Folder Using VBA in Excel (4 Methods)

The below dataset consists of 3 columns: Name, Car Model, and Car Maker. This dataset represents the car information of 6 employees car information for a particular company.

Method 1 – Using Excel VBA to Save an Active Workbook in a Specific Folder

Steps:

Bring up the VBA Module. To do that
- 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.

From Insert >>> select Module.

We will enter our code here.

Enter the following code in 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

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

We will execute the code.

Save this Module.
Click inside our code.
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.

Method 2 – Saving the Workbook in a Specific Folder Using the Save As Dialog Box

Steps:

Bring up the VBA Module, as shown in method 1.
Enter this code inside the 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

We are calling our Sub Procedure Save_Specific_Folder_Selection.
We have defined the variable type.
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.
We set the file type as xlsm.
If we change the filename, it will execute the VBA If Statement to change the filename.

Execute the code, as shown in method 1.

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

Enter a different filename if you want.
Press Save.

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

Read More: Excel VBA Save as File Format

Method 3 – Saving a Specific Workbook in a Specific Folder Using Excel VBA

Steps:

Bring up the VBA Module, as shown in method 1.
Enter this code in the 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

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

Execute the code, as shown in method 1.

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.

Method 4 – Using Excel VBA to Save All Opened Workbooks in a Specific Folder

Steps:

Bring up the VBA Module, as shown in method 1.
Enter this code in the 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

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

Execute the code, as shown in method 1.

It will save the two Workbooks in our specified folder. In conclusion, we have shown four different Excel VBA Macros for saving Workbooks in a specific folder.