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

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.

Related Articles

• How to Save a Worksheet in Excel to a Separate File (3 Handy Methods)
• [Fixed!] Document Not Saved Excel Network Drive (5 Possible Solutions)
• How to Save Multiple Sheets in Excel to CSV (6 Easy Methods)
• Undo a Save in Excel (4 Quick Methods)
• How to Save Excel as PDF Fit to Page (5 Easy Ways)