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


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

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 step-by-step 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: Excel VBA to Save Workbook in Specific Folder with Date


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

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


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

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.


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:

  • 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.

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 Workbook as New File in Same Folder


Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo