How to Save a Worksheet as a New File Using Excel VBA

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

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.

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. You can also save and close the workbook using VBA.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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.

Related Article

• Excel VBA: Save Sheet as New Workbook without Opening