How to Save a Worksheet in Excel to a Separate File (3 Handy Methods)

This article illustrates how to save a worksheet in Excel to a separate file. You may need to export a particular worksheet to a different workbook. Follow the article to learn how to do that. You will also be able to export all of the worksheets to different workbooks by following one of the methods here. Not only that, but you can export multiple worksheets from a workbook to create a different workbook also.

Save a Worksheet in Excel to a Separate File: 3 Ways

1. Save an Excel Worksheet to a Separate File with the ‘Move or Copy‘ Command

Follow the steps below to save a worksheet in Excel to a separate file.

📌 Steps

• First, click on the worksheet tab/name that you want to save as a separate file.
• You can select multiple files to create a separate workbook with them. Use CTRL+Select to do that. Alternatively, select the first worksheet, hold the SHIFT key, and then select the last worksheet to select all of them in between.
• Then, right-click on the worksheet tab. Next, select Move or Copy as shown below.

• Next, select (new book) using the dropdown arrow on the Move or Copy dialog box.
• After that, check the Create a Copy If you do not check this, the selected worksheet(s) will be moved out from the original workbook.
• Now, press the OK button.

• Finally, a new file will be created containing the selected worksheet(s). Now, select File >> save from the newly created workbook to save it in the desired location and as the required file type.

Read More: How to Save a Worksheet in Excel

2. Export an Excel Worksheet to a Separate PDF/XPS File

Follow the steps below to save the selected worksheet in Excel to a separate PDF or XPS file.

📌 Steps

• First, select the worksheet that you want to save to a separate file.
• Then select File >> Export >> Create PDF/XPS Document >> Create PDF/XPS as shown in the following picture.

• Next, browse the desired location where you want to save the file. Then, change the file name. Next, select the file type using the dropdown. Select Options after that.

• Next, make sure the radio button for Active Sheet(s) is selected. Then, select OK. After that, press the Publish button.

Read More: How to Save Multiple Excel Sheets as One File

3. Save Worksheet(s) in Excel to a Separate Workbook with VBA

3.1 Save One Worksheet

You can automate the process using Excel VBA. Follow the steps below to learn how o do that.

📌 Steps

• First, press ALT+F11 to open the VBA window. Then, select Insert >> Module as shown in the picture below. It will create a blank module.

• Next, copy the following code using the copy button in the upper right corner.

Sub SaveActiveWorksheetToSeparateFile()
Dim File_Ext, Location As String
Dim File_Format As Long
Dim wsh As Worksheet
Dim wkb, Nwkb As Workbook
Application.ScreenUpdating = False
Set wkb = Application.ThisWorkbook
Set wsh = ActiveSheet
DtStr = Format(Now, "dd-mm-yyyy hh-mm-ss")
Location = wkb.Path & "\" & wkb.Name & " " & DtStr
If Val(Application.Version) < 12 Then
File_Ext = ".xls": File_Format = -4143
Else
Select Case wkb.FileFormat
Case 51:
File_Ext = ".xlsx": File_Format = 51
Case 52:
If Application.ActiveWorkbook.HasVBProject Then
File_Ext = ".xlsm": File_Format = 52
Else
File_Ext = ".xlsx": File_Format = 51
End If
Case 56:
File_Ext = ".xls": File_Format = 56
Case Else:
File_Ext = ".xlsb": File_Format = 50
End Select
End If
MkDir Location
With wsh
On Error GoTo Error
wsh.Copy
xFile = Location & "\" & wsh.Name & File_Ext
Set Nwkb = Application.Workbooks.Item(Application.Workbooks.Count)
Nwkb.SaveAs xFile, FileFormat:=File_Format
Nwkb.Close False, xFile
End With
Error:
wkb.Activate
MsgBox "The file is in " & Location
Application.ScreenUpdating = True
End Sub

• After that, paste the code onto the module as shown below. Keep the cursor in the code.

• Now, press F5 to run the code. Finally, you will get the desired results.

Read More: How to Save a Copy of an Excel File

3.2 Save All Worksheets

You can use the following code instead to save all of the worksheets to separate files in Excel.

Sub SaveAllWorksheetsToSeparateFiles()
Dim File_Ext, Location As String
Dim File_Format As Long
Dim wsh As Worksheet
Dim wkb, Nwkb As Workbook
Application.ScreenUpdating = False
Set wkb = Application.ThisWorkbook
DtStr = Format(Now, "dd-mm-yyyy hh-mm-ss")
Location = wkb.Path & "\" & wkb.Name & " " & DtStr
If Val(Application.Version) < 12 Then
File_Ext = ".xls": File_Format = -4143
Else
Select Case wkb.FileFormat
Case 51:
File_Ext = ".xlsx": File_Format = 51
Case 52:
If Application.ActiveWorkbook.HasVBProject Then
File_Ext = ".xlsm": File_Format = 52
Else
File_Ext = ".xlsx": File_Format = 51
End If
Case 56:
File_Ext = ".xls": File_Format = 56
Case Else:
File_Ext = ".xlsb": File_Format = 50
End Select
End If
MkDir Location
For Each wsh In wkb.Worksheets
On Error GoTo Error
If wsh.Visible = xlSheetVisible Then
wsh.Select
wsh.Copy
xFile = Location & "\" & wsh.Name & File_Ext
Set Nwkb = Application.Workbooks.Item(Application.Workbooks.Count)
Nwkb.SaveAs xFile, FileFormat:=File_Format
Nwkb.Close False, xFile
End If
Error:
wkb.Activate
Next
MsgBox "The files are in " & Location
Application.ScreenUpdating = True
End Sub

Things to Remember

• You must check the Create a Copy checkbox in the first method. Otherwise, the selected worksheet(s) will be moved out from the original workbook.
• The VBA code saves the files in a new folder in the same location as the original workbook.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to save a worksheet in Excel to a separate file. Hopefully, this article has helped you to do that. Please use the comment section below for further queries or suggestions.

Related Articles

• How to Save Multiple Sheets in Excel
• How to Save Excel File with Password
• How to Save Excel File in XLSX Format
• How to Save Multiple Tabs in Excel as Separate Files

<< Go Back to How to Save Excel File | Excel Files | Learn Excel