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

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


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"

Code Inputs to Save a Worksheet as a New File Using Excel VBA

⧪ 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

Selecting Directory to Save a Worksheet as a New File Using Excel VBA

⧪ 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

Saving the File to Save a Worksheet as a New File Using Excel VBA

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

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

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.

Selecting Folder to Save a Worksheet as a New File Using Excel VBA

Here I’ve selected the folder E:\ExcelDemy on my computer. Then press OK.

It’ll save the file with the new name.

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

Read More: How to Save a Worksheet in Excel (2 Handy Approaches)


Similar Readings


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.

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

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.

Selecting Folder to Save a Worksheet as a New File Using Excel VBA

Here I’ve again selected the folder E:\ExcelDemy on my computer. Then press OK.

It’ll save the file with the new names.

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


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.

Selecting Folder to Save a Worksheet as a New File Using Excel VBA

 

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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1 Comment
  1. Good post. Don’t you need to define the objects Sheet_Name, New_File, and File_Dialog?

Leave a reply

ExcelDemy
Logo