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.


Download Practice Workbook

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


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

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.

Save a Worksheet in Excel to a Separate File

  • 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.

Save a Worksheet in Excel to a Separate File

Read More: How to Save Multiple Excel Sheets as One File (5 Easy Methods)


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

Follow the steps below to save the selected worksheet(s) in excel to a separate PDF/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 Excel as PDF (6 Useful Ways)


Similar Readings


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.

Save a Worksheet in Excel to a Separate File

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

VBA Code Explanation

Sub SaveActiveWorksheetToSeparateFile()
We will write the code inside this subject procedure.

Dim File_Ext, Location As String
Dim File_Format As Long
Dim wsh As Worksheet
Dim wkb, Nwkb As Workbook
Declaring necessary variables.

Application.ScreenUpdating = False
It makes VBA work in the background. As a result, VBA works faster.

Set wkb = Application.ThisWorkbook
Set wsh = ActiveSheet
DtStr = Format(Now, “dd-mm-yyyy hh-mm-ss”)
Location = wkb.Path & “\” & wkb.Name & ” ” & DtStr
It sets values to the variables.

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
Identifying the file format of the original workbook.

MkDir Location
Creates a new folder.

With wsh
Works with the active worksheet.

On Error GoTo Error
Goes to the referred statement in case of errors.

wsh.Copy
Copies the active worksheet.

xFile = Location & “\” & wsh.Name & File_Ext
Set Nwkb = Application.Workbooks.Item(Application.Workbooks.Count)
Nwkb.SaveAs xFile, FileFormat:=File_Format
Nwkb.Close False, xFile
Creates the new file and then saves and closes it.

End With
Stops working with the worksheet.

Error:
The code runs from here if any error occurs with the active worksheet.

wkb.Activate
The original workbook becomes activated.

MsgBox “The file is in ” & Location
A message box displays the location of the newly created file.

Application.ScreenUpdating = True
Screen updating is set to defaults.

End Sub
The subject procedure ends here.


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

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


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.

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. Do visit our ExcelDemy blog to explore more excel related problems and solutions. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

2 Comments
  1. Method 3 is exactly what I needed. Thank you! I placed my Module in my Personal workbook so that it would always be available. Doing so requires that the 7th line be changed to:
    Set wkb = Application.ActiveWorkbook
    This ensures that the path will be the same as the workbook containing the active sheet, instead of the path to the default Personal workbook. I dabble in VBA but not enough to have written a subroutine like yours, so thank you again!

Leave a reply

ExcelDemy
Logo