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.

excel vba save workbook in specific folder Main Dataset


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

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.

Developer Tab: excel vba save workbook in specific folder

  • 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

excel vba save workbook in specific folder

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.

Run Code: excel vba save workbook in specific folder

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.

Read More: How to Save a Worksheet in Excel (2 Handy Approaches)


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:

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

Save Workbook in Specific Folder by Utilizing Save As Dialog Box

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.

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.

Final Output: Save Workbook in Specific Folder by Utilizing Save As Dialog Box

Read More: Excel VBA Save as File Format (12 Suitable Examples)


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:

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

Save Specific Workbook in Specific Folder by Using Excel VBA

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.

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.

Read More: Excel VBA: Save and Close Workbook (5 Suitable Examples)


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.

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

Steps:

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.

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.

Final Step of Use of Excel VBA to Save All Opened Workbooks in Specific Folder

Read More: Excel VBA: Save Sheet as New Workbook without Opening


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!


Related Articles

Rafiul Haq

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo