Rifat Hassan
One of many great uses of Excel VBA in our day-to-day life is that we can use it to save a worksheet as a new Excel file by running just a few lines of code. In this article, I’ll show you how you can save a worksheet as a new file using VBA in Excel.
Excel VBA: Save a Worksheet as a New File (Quick View)
Sub Save_Worksheet_as_New_File()
Sheet_Name = "Sheet1"
New_File = "New_Sheet1"
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Directory to Save the File"
If File_Dialog.Show <> -1 Then
   Exit Sub
End If
Full_Name = File_Dialog.SelectedItems(1) + "\" + New_File
Worksheets(Sheet_Name).SaveAs Filename:=Full_Name
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Overview of the VBA Code to Save a Worksheet as a New File (Step-by-Step Analysis)
So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to save a worksheet as a new file in detail.
⧪ Step 1: Inserting the Necessary Inputs
At the very outset of the code, we have to insert the necessary input into it. There are only two inputs required in this code. One is the name of the worksheet that we are going to save, and the other is the name of the new file in which we want to be saved.
For the sake of this example, I have a worksheet called Sheet1. I want to save it with the name New_Sheet1.
So the lines of the code for me will be:
Sheet_Name = "Sheet1"
New_File = "New_Sheet1"
⧪ Step 2: Selecting the Directory
Next, we’ll select the directory on our computer where we want to save the new file. We’ll use a FileDialog object for the purpose.
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Directory to Save the File"
If File_Dialog.Show <> -1 Then
   Exit Sub
End If
⧪ Step 3: Combining the Directory Name with the File Name
Next, we’ll combine the new file name with the selected directory name to get the full name of the file.
Full_Name = File_Dialog.SelectedItems(1) + "\" + New_File
⧪ Step 4: Saving the File
Now the most important step. We’ll save the worksheet as a new file with the given name in the selected folder, using the SaveAs method of VBA.
Worksheets(Sheet_Name).SaveAs Filename:=New_File
So the complete VBA code will be:
⧠VBA Code:
Sub Save_Worksheet_as_New_File()
Sheet_Name = "Sheet1"
New_File = "New_Sheet1"
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Directory to Save the File"
If File_Dialog.Show <> -1 Then
   Exit Sub
End If
Full_Name = File_Dialog.SelectedItems(1) + "\" + New_File
Worksheets(Sheet_Name).SaveAs Filename:=Full_Name
End Sub
⧠Output:
Run the code. First, a FileDialog box will appear asking you to select the directory in which you want the file to be saved.
Here I’ve selected the folder E:\ExcelDemy on my computer. Then press OK.
It’ll save the file with the new name.
Read More: How to Save a Worksheet in Excel (2 Handy Approaches)
Similar Readings
- Excel VBA: Save Workbook in Specific Folder (4 Suitable Examples)
- How to Open Word Document and Save As PDF or Docx with VBA Excel
- How to Undo Changes in Excel after Save and Close (2 Easy Methods)
- [Fix]: Microsoft Excel Cannot Open or Save Any More Documents Because There Is Not Enough Available Memory
- Save Excel as CSV with Double Quotes (3 Simplest Methods)
Developing Macros to Save One or More Worksheets as One or More New Files Using Excel VBA
We’ve learned the basic code to save a worksheet as a new file using VBA. Now we’ll develop a few Macros to save one or more worksheets as new files on our computers.
Here we’ve got a workbook with 5 worksheets, called Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5 respectively.
We’ll develop Macros to save one or more worksheets from this workbook as new files.
1. Developing a Macro to Save Some Selected Worksheets as New Files
Now, we’ll do one more thing. Let’s try to save the worksheets Sheet1, Sheet2, and Sheet3 with the names New_Sheet1, New_Sheet2, and New_Sheet3 respectively.
You can use the following VBA code for the purpose.
⧠VBA Code:
Sub Save_Selected_Worksheets_as_New_Files()
Sheet_Names = Array("Sheet1", "Sheet2", "Sheet3")
File_Names = Array("New_Sheet1", "New_Sheet2", "New_Sheet3")
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Directory to Save the File"
If File_Dialog.Show <> -1 Then
   Exit Sub
End If
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
   Full_Name = File_Dialog.SelectedItems(1) + "\" + File_Names(i)
   Worksheets(Sheet_Names(i)).SaveAs Filename:=Full_Name
Next i
End Sub
⧠Output:
Run the code by changing the inputs. First, a FileDialog box will appear asking you to select the directory in which you want the files to be saved.
Here I’ve again selected the folder E:\ExcelDemy on my computer. Then press OK.
It’ll save the file with the new names.
2. Running a Macro to Save All the Worksheets as New Files
Finally, we’ll develop a code to save all the worksheets of the workbook as new files. This time we’ll not change the names of the sheets. That means the names of the new files and the worksheets will remain identical.
You can use the following VBA code.
⧠VBA Code:
Sub Save_All_Worksheets_as_New_Files()
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Directory to Save the File"
If File_Dialog.Show <> -1 Then
   Exit Sub
End If
For i = 1 To Sheets.Count
   Full_Name = File_Dialog.SelectedItems(1) + "\" + Worksheets(i).Name
   Worksheets(i).SaveAs Filename:=Full_Name
Next i
End Sub
⧠Output:
Run the code. First, a FileDialog box will appear asking you to select the directory in which you want to save the files.
I’ve again selected the folder E:\ExcelDemy on my computer. Then press OK.
It’ll save all the sheets from your active workbook as new files inside the selected folder.
Read More: How to Save Multiple Excel Sheets as One File (5 Easy Methods)
Conclusion
Therefore, this is the process to save a workbook as a new file using Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Good post. Don’t you need to define the objects Sheet_Name, New_File, and File_Dialog?
Hi Tim, thank you for your valuable response. You can declare the variables this way:
Dim File_Dialog as FileDialog
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
Dim Sheet_Names(2) as Variant
Sheet_Names(0)=”Sheet1″
Sheet_Names(1)=”Sheet2″
Sheet_Names(2)=”Sheet3″
Dim New_File(2) as Variant
New_File(0)=”New_Sheet1″
New_File(1)=”New_Sheet2″
New_File(2)=”New_Sheet3″
But declaring variables beforehand is not mandatory in VBA. You can directly assign values to them as mentioned in the given codes.