The sample Excel workbook contains 3 worksheets: ‘VBA-1’, ‘VBA-2’ and ‘VBA-3’. Each worksheet contains the same dataset (B4:D8).
To save a new file in this same folder using Excel VBA:
Example 1 – Using Excel VBA to Save the Active Workbook as a New File in the Same Folder
Steps:
- Go to the Developer tab.
- In Code, click Visual Basic.
- In the Microsoft Visual Basic for Applications window, click Insert and select Module.
- Enter the VBA code below.
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- Click Save.
- In the Microsoft Excel window, click Yes to save the file as a macro-free workbook.
- Keep the cursor inside the code and click Run.
A new ‘.xlsx’ file is saved in the same folder.
It will not contain any data from the original book. You will get a blank workbook.
Read More: Excel VBA to Save Workbook in Specific Folder with Date
Example 2 – Save a Copy of a Workbook in the Same Folder as the Original Using VBA
Steps:
- Open the VBA code window by following the steps described in the previous method.
- Enter 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- Click Save.
- Click Yes in the Microsoft Excel window to save the file as ‘.xlsx’.
- Place the cursor inside the code and click Run.
A new ‘.xlsx’ file is saved in the same folder.
It is a copy of the workbook.
Read More: How to Save a Copy as XLSX Using Excel VBA
Example 3 – Using Excel VBA to Save a Workbook in the Same Folder in the Save As Dialog Box
Steps:
- Save the Excel Workbook (.xlsx) as an Excel Macro-Enabled Workbook (.xlsm): open the Excel Workbook and go to the File tab.
- Click Save As.
- Go to Save as type > Excel Macro-Enabled Workbook >Save.
- Save the workbook in a folder.
- Save a copy of the workbook in the same folder: open the VBA code window as described in the previous examples.
- 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- Click Save > place the cursor inside the code > click Run.
- In the Save As dialog box, you can change the File name.
- Click Save.
The workbook was saved in the same folder.
The new workbook contains the data in the original workbook.
Read More: Excel VBA: Save Workbook in Specific Folder
Download Practice Workbook
Download the practice workbook here.
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
