How to export and save each worksheet as separate new workbook in Excel?

Let's say you want to export and save one or several worksheets as a new workbook, how will you deal with it? Normally you may copy each worksheet and paste to new workbook. But here we bring you some handy tricks to deal with it:

Export and save worksheets as new workbook with Move or Copy command

Export and save worksheets as new workbook with VBA code

Export and save worksheets as new workbook with Kutools for Excel

Quickly split multiple worksheets into separate workbook in Excel

In Microsoft Excel, you can save or split a worksheet from one workbook as a new Excel file by copying and pasting this worksheet into a new workbook. It seems troublesome, if you want to split each sheet / worksheet of a large workbook as separate Excel, txt, csv, pdf files. But with Kutools for Excel’s Split Workbook utility, you can quickly deal with it.  Click for 60 days free trial with full features!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

Export and save worksheets as new workbook with Move or Copy command

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly.

Step 1: Select the worksheet names in tab bar. You can select multiple with holding down Ctrl key or shift key.

Step 2: Right click the worksheet name, and click the Move or Copy from context menu.

Step 3: In Move or Copy dialog box, select the (new book) item from the drop down list of Move selected sheets to book.

Step 4: Then click OK, now you have switched to the new workbook with exported or copied worksheets, click File > Save to save the new workbook.

Note: In the Move or Copy dialog box, there is a Create a copy option. If you do not check it, it will move the selected worksheets out of original workbook; if check it, it will copy selected worksheets.

Export and save worksheets as new workbook with VBA code

This following code will export every visible worksheet in a new workbook and save the workbook with the name of the original sheet in a newly created folder in the same path as the active workbook. Please do as following steps:

Step 1: Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

Step 2: Click Insert > Module, and paste the following macro in the Module Window:

VBA : Export and save worksheets as new workbook in a new folder.

Sub SplitWorkbook()
'Updateby20140612
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "\" & xWb.Name & " " & DateString
MkDir FolderName
For Each xWs In xWb.Worksheets
    xWs.Copy
    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        Select Case xWb.FileFormat
            Case 51:
                FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If Application.ActiveWorkbook.HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56:
                FileExtStr = ".xls": FileFormatNum = 56
            Case Else:
                FileExtStr = ".xlsb": FileFormatNum = 50
        End Select
    End If
    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
Next
MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End Sub

Step 3: Press the F5 key to run this code. And a prompt box will pop up to tell you the location of the new exported workbooks, and all of the worksheets of the original workbook have been exported to some new separate workbooks which named original sheets in a new specific folder. See screenshots:

Export and save worksheets as new workbook with Kutools for Excel

If you have multiple worksheets needed to be saved as separate workbook, the first method cannot be a good choice. And the VBA code maybe a little complicated for Excel beginners. Here you can use the Split Workbook utility of Kutools for Excel to quickly and easily export and save each worksheet of one workbook to separate new workbook.

After free installing Kutools for Excel, please do as below:

Step 1: Click Kutools Plus > Workbook > Split Workbook…. See screenshot:

Step 2: In the Split Workbook dialog box, do as below:

1: all of the worksheet names have been checked by default, if you don’t want to split some of the worksheets, you can uncheck them;


2: If you want to avoid splitting the hidden or blank worksheets, you can check the Skip hidden worksheets or Skip blank worksheets.);


3: and also you can check Specify save format, and to select split the sheets then save as text file, pdf file, or csv file, in defualt, if you do not check this option, it will split sheets and save as workbook.

Step 3: Then click Split button, and choose the folder that you want to put the new workbooks to. See screenshot:

Step 4: And then click OK, then each checked worksheet in the Split Workbook dialog box is exported and saved as individual workbook. Each new workbook is named with the original worksheet name. See screenshot:

Click to know more information about this Split Workbook tool.

Export and save each sheet as new workbook

Quickly split data into multiple worksheets based on column or fixed rows in Excel

Supposing you have a worksheet that has data in columns A to G, the salesman’s name is in column A and you need to automatically split this data into multiple worksheets based on the column A in the same workbook and each salesman will be splitted into a new worksheet. Kutools for Excel’s Split Date utility can help you to quickly split data into multiple worksheets based on selected column as below screenshot shown in Excel.  Click for full future 60 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

Related articles:

• Combine multiple workbooks to single workbook
• Save a selection as individual file
• Split a workbook to separate Excel files

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel