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 files with Kutools for Excel easily

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Split a workbook to separate Excel files with copying and pasting

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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.


arrow blue right bubble 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 & ".xls"
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 have been split to separate Excel files in the same folder as the original workbook. See screenshot:

Note: If one of the sheets is named as the same as the workbook, this VBA cannot work.


arrow blue right bubble Split a workbook to separate Excel 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 includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

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

2. In the Split Workbook dialog box, 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. (If you want to avoid splitting the hidden or blank worksheets, you can check the Skip hidden worksheets or Skip blank worksheets.) See screenshot:

3. Then click Split button, in the popping up dialog box, choose a location for the new Excel files.

4. And then click OK, the checked worksheets will be saved as new separated workbooks quickly. 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…


Related articles:

Combine multiple workbooks to single workbook

Save a selection as individual file


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

-1#Dhanasekar Sakthivel2013-12-30 15:03
I have data's in 4 different sheet. Sheet1 is named as "A", Sheet2 is named as "B", Sheet3 is named as "C" and Sheet4 is named as "D".
Need to split the data into different workbooks and each sheet must have only 25 rows. The file name should be named as Sheet1 A.xls, Sheet2 A.xls if the data is copied from Sheet A. Once the Sheet is A become empty, it must copy the data from Sheet B and must continue the Sheet number from last e.g Sheet3 B.xls.
Reply | Reply with quote | Quote
0#Rebecca2014-01-20 06:00
When I "Split a workbook to separate Excel files with VBA code" it works a dream but does not keep formatting such as merged cells and cell alignment in the new spreadsheets.
Is there something I can add so the formatting is kept the same as the originals?
Reply | Reply with quote | Quote
0#Daniel2014-02-05 18:11
The code worked like a charm!
Yes, not all formatting were kept.
No problem, though. For me, all the merged cells in a table became un-merged. It was a simple thing to select the original table, format painter, highlight the copied table, and voila, good as new.

Thanks!
Reply | Reply with quote | Quote
0#Chandrasekar T2014-03-04 04:43
Column A IP Address
Column B IP Name
Column C IP Pinging Rate True (That is command Pinging Status)
Column D IP Pinging Rate False (That is command Pinging Status)
This ip and ip name list to excel format & status is same excel format(that is true or false Status)
Reply | Reply with quote | Quote
0#terp2014-04-24 20:59
Awesome code - saved the day for me. Thank you for posting.
Reply | Reply with quote | Quote
0#KP2014-05-05 17:20
I am having an issue adding code to save the split files as ReadOnly. Why does this not work or how do I do this?


Sub Splitbook()
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & sht.Name & ".xls",
Password:="",
WriteResPassword:="",
ReadOnlyRecommended:=False,
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub
Reply | Reply with quote | Quote
0#Shatrughan singh2014-05-08 12:19
Really superb facility.., amazing...wow..
Reply | Reply with quote | Quote
0#RAVISHANKAR2014-05-23 10:15
Awesome script.It's worked for me like anything. Thanks.
Reply | Reply with quote | Quote
0#Starscor2014-06-02 01:58
Useful script, but i'm looking for further steps, to self-pick a critera from a excel and auto-populate into multiple files
Reply | Reply with quote | Quote
0#joy2014-06-08 05:39
Thank you method 1 was extremely useful
Reply | Reply with quote | Quote
+1#Frankie2014-06-17 02:37
Thank you! Saved me a lot of manual effort!
Reply | Reply with quote | Quote
0#Dodger2014-06-24 21:30
VBA instructions were "spot on"! ... even for a novice!
Worked great!
Reply | Reply with quote | Quote
0#Jennifer2014-07-15 08:57
Very useful! Thanks for sharing
Reply | Reply with quote | Quote
0#ADO2014-07-20 19:38
Great help!!! extremely easy and useful :-)
Reply | Reply with quote | Quote
0#revati2014-08-11 19:13
Used this and it worked. Just what I needed. Thanks
Reply | Reply with quote | Quote

Add comment


Security code
Refresh