Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to split a workbook to separate Excel files in Excel?

You may need to split a large workbook to separate Excel files with saving each worksheet of the workbook as an individual Excel file. For example, you can split a workbook into multiple individual Excel files and then deliver each file to different person to handle it. By doing so, you can get certain persons handle specific data, and keep your data safe. This article will introduce ways to split a large workbook to separate Excel files based on each worksheet.

Split a workbook to separate Excel files with copying and pasting

Split a workbook to separate Excel files with VBA code

Split a workbook to separate Excel / PDF / CSV / TXT files with Kutools for Excel easily


Split a workbook to separate Excel files with copying and pasting


In usual, using Copy command and Paste command can save a workbook as a Separate Excel file manually. Firstly, select the whole worksheet that you want to save as a separate file, create a new workbook, and then paste it in the new workbook, at the end saves it.

This way is easy-to-use if you need to split only a few worksheets as separate files. However, it must be time-consuming and tedious to split many worksheets with copying and pasting manually.


Split a workbook to separate Excel files with VBA code

The following VBA code can help you quickly split multiple worksheets of current workbook to separate Excel files, please do as follows:

1. Create a new folder for the workbook that you want to split, because the split Excel files will be stayed at the same folder as this master workbook.

2. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA: Split a workbook into multiple workbooks and save in the same folder.

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

4. Press the F5 key to run this code. And the workbook is split to separate Excel files in the same folder with the original workbook. See screenshot:

Note: If one of the sheets has the same name with the workbook, this VBA cannot work.


Split a workbook to separate Excel / PDF / CSV / TXT files with Kutools for Excel easily

If you have Kutools for Excel installed, its Split Workbook tool can split multiple worksheets as separate Excel files conveniently and quickly with only a few clicks.

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

1. After installing Kutools for Excel, click Enterprise > Workbook > Split Workbook , see screenshot:

2. In the Split Workbook dialog box, do the following operations:

(1.) All worksheet names are checked by default. If you don’t want to split some of the worksheets, you can uncheck them;

(2.) From the Save as type drop down, choose one file type you want to split and save.

(3.) Then click Split button, and in the popping out Browse For Folder dialog box, please specify a file folder to locate the separate files.

Note: If you want to avoid splitting the hidden or blank worksheets, you can check the Skip hidden worksheets or Skip blank worksheets box.

3. And then click OK to start splitting, now the selected worksheets are saved as new separated workbooks. Each new workbook is named with the original worksheet name. See screenshot:

Kutools for Excel's Split Workbook tool makes it easy to split active workbook into individual Excel files (one file contains one worksheet), and each Excel file carries the name of the worksheet. You can set to skip all the blank and hidden worksheets. Read more…

Click Download and free trial Kutools for Excel Now !


Demo: Split or save each worksheet of one workbook as separate excel / txt / csv / pdf files

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related articles:

Combine multiple workbooks to single workbook

Save a selection as individual file



Recommended Productivity Tools

Office Tab

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

Kutools for Excel

gold star1Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    alfonso · 1 months ago
    getting runtime error 1004
  • To post as a guest, your comment is unpublished.
    Web7 · 3 months ago
    Does anyone know how to make this code work for a mac?
    It is showing an error when trying to locate the document.
  • To post as a guest, your comment is unpublished.
    Ronnie · 3 months ago
    Amazing script and instructions. Just saved me from splitting 30+Worksheets to separate files
    • To post as a guest, your comment is unpublished.
      Kim · 1 months ago
      Same! This worked perfectly for me. Saved me a lot of effort. Thank you!
  • To post as a guest, your comment is unpublished.
    Judy Smith · 7 months ago
    Please disregard my previous post. I have resolved my issue.
  • To post as a guest, your comment is unpublished.
    Judy Smith · 7 months ago
    I am using the split data into worksheets based on a column with 27 items in that column. The results are 54 worksheets 2 for each item. The only difference I can see is based on one of the columns in the range. For example:

    Once the split has occured, John Doe has 2 worksheets, one named John Doe and another named Sheet32. Each sheet has information for John Doe, but based on a column "Status" (Billed, Open Order, Salesforce), the information is split. The named worksheet has Billed & Open Order information and the Sheet32 has Salesforce information.


    Any idea what I am doing wrong?