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.
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.
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.
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:
- 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.
Read More: Excel VBA Save as File Format (12 Suitable Examples)
Similar Readings
- How to Save Macros in Excel Permanently (2 Suitable Ways)
- Save Excel as PDF Fit to Page (5 Easy Ways)
- How to Undo a Save in Excel (4 Quick Methods)
- Save Macro to Personal Macro Workbook?
- How to save workbooks that contain Macros
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.
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.
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.
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
- Excel VBA to Print As PDF and Save with Automatic File Name
- Fix Excel Not Responding and Save Your Work
- How to Open Word Document and Save As PDF or Docx with VBA Excel
- [Fix]: Microsoft Excel Cannot Open or Save Any More Documents Because There Is Not Enough Available Memory
- How to Save Excel as PDF without Cutting Off (4 Suitable Ways)
- Excel VBA: Choose Location and Save as PDF