Sometimes, we may need to save an Excel workbook as a new file in the same folder. We usually do this by using the Save As option in the Excel File tab. But we can also save the workbook as a new file in the same folder by using Excel VBA very quickly. This article will help you to do this task with some ideal examples.
Excel VBA to Save Workbook as New File in Same Folder: 3 Ideal Examples
In this article, we will see 3 excellent examples with explanations of saving a workbook to the same folder as the original using Excel VBA. Here, we can see an Excel workbook that contains 3 worksheets named ‘VBA-1’, ‘VBA-2’ and ‘VBA-3’ respectively. Besides, each worksheet contains the same dataset (B4:D8) the same as the screenshot below.
However, in the screenshot below, we can see the location and folder of our Excel workbook. Now, we need to save a new file in this same folder by using Excel VBA. So, without further delay, let’s get started.
1. Excel VBA to Save Active Workbook as New File in Same Folder
In this method, we will learn to save an active workbook as a new file in the same folder as the original by using Excel VBA. To execute this method, first, we need to open our Excel file as an active workbook. Then we need to follow the steps below.
Steps:
- In the first place, go to the Developer tab.
- Secondly, find the Code group and click on Visual Basic.
- Therefore, Microsoft Visual Basic for Applications window will appear.
- Next, click on Insert and select Module from the dropdown.
- After that, the Code window will open and you need to insert the VBA code below there.
Sub saving_new_file()
Dim thisfile As Workbook
Set thisfile = ActiveWorkbook
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=thisfile.Path & "\new workbook1.xlsx"
ActiveWorkbook.Close savechanges:=False
End Sub- At this time, click on the Save To see its position look at the screenshot below.
- In turn, a window named Microsoft Excel will pop up and you need to click the Yes option there to save it as a macro-free workbook.
- Now, you need to keep the cursor inside the code and then click on the Run button. You can see the screenshot below for a better understanding.
- In this way, you will be able to save a new ‘.xlsx’ file in the same folder as the original one. See the screenshot below.
- In this case, the new file will not contain any data from the original one. That means we will get a blank workbook like the screenshot below by applying this VBA code.
Read More: Excel VBA to Save Workbook in Specific Folder with Date
2. Save a Copy of Workbook to Same Folder as Original Using VBA
In the previous method, we could save a blank workbook in the same folder as the original one. By applying the VBA code of this method, we will be able to save a copy of the workbook using Excel VBA in the same folder containing all the data. To do so, we need to follow the steps below.
Steps:
- First, you need to open the VBA code window by following the steps shown in the previous method.
- Next, type the VBA code below in the code window.
Sub saving_new_file()
Dim path As String
Dim workbookname2 As String
path = ThisWorkbook.path & "\"
workbookname2 = Range("Z40").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=path & workbookname2 & "new workbook2.xlsx"
Application.DisplayAlerts = True
End Sub- Now, click on the Save button.
- Then, click the Yes option in the Microsoft Excel window to save the file as a ‘.xlsx’ type.
- Later, put the cursor inside the code and click on the Run option.
- Thus, we will be able to save a new ‘.xlsx’ file in the same folder as the original one.
- However, in this approach, we have saved a copy of the workbook that is exactly like the original one in the same folder. The screenshot below is proof of this.
Read More: How to Save a Copy as XLSX Using Excel VBA
3. Excel VBA to Save Workbook in Same Folder with Save As Dialog Box
In order to save Workbook in the same folder, here, we will use the File Save As dialog box inside of the VBA code.
Steps:
- To execute this method, in the beginning, you need to save the Excel Workbook (.xlsx) as an Excel Macro-Enabled Workbook (.xlsm). For this, you need to open the Excel Workbook and go to the File tab.
- Then, click on the Save As option on the left side.
- After that, go to Save as type > Excel Macro-Enabled Workbook >Save.
- Next, save the workbook in a folder as you wish. For example, the location of our file is given in the picture below.
- Now, we need to save a copy of this workbook in the same folder. To do this, open the VBA code window like the previous methods.
- Subsequently, enter the VBA code below in the code window.
Sub SavingNewFile()
Dim y1 As String
Dim y2 As Variant
y1 = "new workbook3"
y2 = Application.GetSaveAsFilename(InitialFileName:=y1, _
FileFilter:="File Type (*.xlsm), *.xlsm")
If y2 <> False Then
ActiveWorkbook.SaveAs y2
End If
End Sub- Therefore, click Save > put cursor inside code > click Run.
- In turn, a Save As dialog box will open.
- If you want, you can change the File name here or use the one specified in the code.
- In the end, click on Save.
- Thus, we can save a new workbook in the same folder as the original.
- Here, the new workbook contains all the data that were in the original workbook. See the screenshot below.
Read More: Excel VBA: Save Workbook in Specific Folder
Download Practice Workbook
Download the practice workbook from here.
Conclusion
I hope the above methods will be helpful for you to use Excel VBA to save a workbook as a new file in the same folder. Download the practice workbook and give it a try. Let us know your feedback in the comment section.
Related Articles
- Excel VBA: Save Workbook Without a Prompt
- Excel VBA to Save File with Variable Name
- How to Use Macro to Save Excel File with New Name
- Excel VBA Save as File Format
- Excel VBA to Save as File Using Path from Cell
- How to Save Excel Macro Files as Filename from Cell Value
- Create New Workbook and Save Using VBA in Excel
