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

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

Steps

• 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 select the last worksheet to select all the sheets in between them.
• Right-click on the worksheet tab. Select Move or Copy.

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

• A new file will be created containing the selected worksheet(s).
• 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

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

Steps

• Select the worksheet that you want to save to a separate file.
• Select File >> Export >> Create PDF/XPS Document >> Create PDF/XPS.

• Browse the desired location where you want to save the file. Change the file name. Select the file type using the dropdown. Select Options.

• Make sure the radio button for Active Sheet(s) is selected. Click OK. Press the Publish button.

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

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

3.1 Save One Worksheet

Steps

• Press ALT+F11 to open the VBA window. Select Insert >> Module. It will create a blank module.

• Copy the following code.

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

• Paste the code in the module as shown below. Keep the cursor in the code.

• Press F5 to run the code.

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

3.2 Save All Worksheets

Use the following code 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. If not checked, 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

Related Articles

• How to Save Multiple Sheets in Excel
• How to Save Excel File with Password
• How to Save Excel File in XLSX Format

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