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


arrow blue right bubble 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.


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


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 > Split Workbook , see screenshot:

2. In the Split Workbook dialog box, all worksheet names are checked by default. If you don’t want to split some of the worksheets, you can uncheck them, and finally click the Split button. See screenshot:

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. In the following popping up Browse For Folder dialog box, choose a location for saving the new split Excel files, and then click the OK button.

4. 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…


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 star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 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

btn read more      btn download     btn purchase

Comments  

Permalink -2 Dhanasekar Sakthivel
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.
2013-12-30 15:03 Reply Reply with quote Quote
Permalink +2 Rebecca
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?
2014-01-20 06:00 Reply Reply with quote Quote
Permalink 0 Daniel
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!
2014-02-05 18:11 Reply Reply with quote Quote
Permalink +2 Chandrasekar T
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)
2014-03-04 04:43 Reply Reply with quote Quote
Permalink +2 terp
Awesome code - saved the day for me. Thank you for posting.
2014-04-24 20:59 Reply Reply with quote Quote
Permalink +3 KP
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:="",
WriteResPasswor d:="",
ReadOnlyRecomme nded:=False,
ActiveWorkbook. Close savechanges:=False
Next sht
End Sub
2014-05-05 17:20 Reply Reply with quote Quote
Permalink -1 Shatrughan singh
Really superb facility.., amazing...wow..
2014-05-08 12:19 Reply Reply with quote Quote
Permalink -1 RAVISHANKAR
Awesome script.It's worked for me like anything. Thanks.
2014-05-23 10:15 Reply Reply with quote Quote
Permalink -1 Starscor
Useful script, but i'm looking for further steps, to self-pick a critera from a excel and auto-populate into multiple files
2014-06-02 01:58 Reply Reply with quote Quote
Permalink 0 joy
Thank you method 1 was extremely useful
2014-06-08 05:39 Reply Reply with quote Quote
Permalink 0 Frankie
Thank you! Saved me a lot of manual effort!
2014-06-17 02:37 Reply Reply with quote Quote
Permalink -1 Dodger
VBA instructions were "spot on"! ... even for a novice!
Worked great!
2014-06-24 21:30 Reply Reply with quote Quote
Permalink -1 Jennifer
Very useful! Thanks for sharing
2014-07-15 08:57 Reply Reply with quote Quote
Permalink -2 ADO
Great help!!! extremely easy and useful :-)
2014-07-20 19:38 Reply Reply with quote Quote
Permalink 0 revati
Used this and it worked. Just what I needed. Thanks
2014-08-11 19:13 Reply Reply with quote Quote
Permalink 0 Lara
Works great, thanks. I have created this as an add-in and installed it. How do I now run it whenever I want to (without having to copy and paste each time)? Is there a button I push or a function name I use?
2014-11-05 00:49 Reply Reply with quote Quote
Permalink 0 Suzi
Its Showing error in the line 8....
xws cpy is error
2015-01-23 08:14 Reply Reply with quote Quote
Permalink 0 Marie
It worked for me after I removed the Option Explicit, so the variable needed to be defined.
However, I would like to know what this variable should be defined as, as I appreciate the Option Explicit option :)
2016-07-26 07:43 Reply Reply with quote Quote
Permalink 0 Sanjay
This code & the KUtools both shows an error after converting 222 files & after 222 files both stop working.
2015-02-13 09:12 Reply Reply with quote Quote
Permalink 0 nayeem khan
Dear Sir,

Can your please send me details how coding to split s
2015-03-07 07:08 Reply Reply with quote Quote
Permalink 0 Sanjay
You can follow the below coding.

VBA Coding for split sheets
Alt+f11
Insert-module-f5


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
2015-03-28 06:07 Reply Reply with quote Quote
Permalink 0 denise
Worksheets were product of pivot table report, new files created by the code still retain connection to data source and can be changed.
can code be done to break pivot table connection, or add a copy all of each sheet & past as values to rid the connection?
2015-09-04 00:29 Reply Reply with quote Quote
Permalink 0 Rony
Dear Mr Sanjay,

I tried with the codes it works only for few sheets but the file I am trying to split having almost 466 sheets. May I know the codes for it? Thank you.
2016-12-11 12:28 Reply Reply with quote Quote
Permalink -1 Yasir Arfat
Can you please tell me that how can i send mass emails to with personalized attachment in outlook..

Regrads,
Yasir
2015-03-24 18:32 Reply Reply with quote Quote
Permalink -1 Sanjay
Hi Yasir,

You can't send personalized attachment in mail merge with outlook.

Yes, few external tools are there but these are very costly also not reliable.

You can do this with Mozilla Thunderbird.

Just need to download mail merge add on & you can do this.
2015-03-28 06:06 Reply Reply with quote Quote
Permalink 0 Krishna
It's really Useful. Thanks for sharing.
2015-04-01 11:15 Reply Reply with quote Quote
Permalink 0 s
Superb Code. Actually searching for this.
2015-04-26 09:42 Reply Reply with quote Quote
Permalink 0 Andrew
I have to say I LOVE Kutools for Excel, it saves me so much time everyday!

One questions on the above process, is there an option to not open every sheet once it's created and just save it into the designated folder?
2015-05-08 04:04 Reply Reply with quote Quote
Permalink -1 Fayyadh
Its not work
Stuck at xWs.Copy
what i suppose to do?

Thanks :-)
2015-06-11 04:55 Reply Reply with quote Quote
Permalink -1 Karen
I'm having this same problem now. It worked fine in previous months, but for some reason is not now. Did you figure out how to fix?
2015-09-23 15:15 Reply Reply with quote Quote
Permalink 0 Maggie
I receive a Run time error '1004'. Is this due to me using a pivot table? Is there a way around this?
2015-07-01 16:01 Reply Reply with quote Quote
Permalink -2 Shiva-India
Dear Maggie,

I also get Run time error '1004'. But again I tried and succeed. The error because of I hide one sheet. After unhide that sheet, I got. Check once if it is same case.
2015-07-28 07:23 Reply Reply with quote Quote
Permalink -1 John Boyd
Good stuff! Thank you!
2015-08-18 17:23 Reply Reply with quote Quote
Permalink -1 Caroline
Hi - I have a work sheet with a lot of data that I need to split into separate sheets so I can attach and email to various people. Do you know how I would go about this? I have a TOTAL cell for each batch of data that I need to split, not all the batches contain the same number of lines. Any help you can give would be greatly appreciated. Thank you.
2015-09-03 15:39 Reply Reply with quote Quote
Permalink -1 Sam
I am having data for all the 365 days for 14 year in one excel sheet.

Now we want to separate the data year wise (Ex. one excel file for year 2002 in that 12 Sheet ie for 12 month and another excel file for year 2003 in that 12 Sheet ie for 12 month).

We are able to separate month wise data (by using Excel Kutools - Split Data) and it will create excel sheet for each month (ie it will create totally 24 sheets) and then we should Split to Workbook and it will be 24 excel file and again we should combine 12 file for each year. This is taking lot of time.

Now our problem is while splitting the date we want Excel to create Year wise Excel files and in each excel file 12 sheets for 12 months. Can we do this in same time.

Please tell us because we are having lots of data and it is taking lot of time.
2015-09-22 05:20 Reply Reply with quote Quote
Permalink -1 Brian
Hello, Thank you; super helpful. I was curious how to edit the save directory. When I run this code, it saves one directory up from the directory the original file is saved. Can someone please tell me how to make it save to the source file's directory?
2015-09-24 20:02 Reply Reply with quote Quote
Permalink -1 Ovi
Is there a way I can apply the split to many excel files at once? The files have the same 2 sheets (as format and name).
Thank you!
2015-11-26 14:39 Reply Reply with quote Quote
Permalink -1 NG
you saved my day with this code!!
2015-12-01 21:21 Reply Reply with quote Quote
Permalink 0 zirokl
Thank you so much for this! It is amazing, works perfectly.

But is it possible to modify it so it saves each worksheet to the same directory(witho ut creating a new folder) based on original file name or particular Cell Value? Just by adding (1), (2) ets at the end of each exported file.

Greatly appreciate your help.
2015-12-03 00:59 Reply Reply with quote Quote
Permalink -2 NG
Hi there,

Thank you so much for the code, however may how you would add to the code if you wanted the following to happen?
1. What if you wanted Sheet 1 to be copied as well for each additional sheets?

For example, the newly created
1. "Sheet 2" would have "Sheet 1 and Sheet 2"
2. "Sheet 3" would have "Sheet 1 and Sheet 3"

and so on and so forth.

Appreciate if you could provide any help with this. Thank you.
2015-12-20 23:40 Reply Reply with quote Quote
Permalink 0 Herb
Thanks for the VBA code!!. It worked perfectly. Saved me much work!!
2016-03-22 16:49 Reply Reply with quote Quote
Permalink 0 Adrian Rees
Thanks, vba code worked perfectly! Saved me a lot of time splitting a spreadsheet with 25 tabs into different files.

Only thing i picked up is in the 6th last line i changed the file format to new Excel "xlsx"

" & xWs.Name & ".xls" becomes >>>>> " & xWs.Name & ".xlsx"
2016-03-23 03:43 Reply Reply with quote Quote
Permalink +1 Stephanie
The VBA code worked beautifully! It saved me so much time. Thank you for sharing!
2016-04-15 17:59 Reply Reply with quote Quote
Permalink 0 Anandaraj.M
Thanks for your valuable support
2016-06-17 10:45 Reply Reply with quote Quote
Permalink 0 Rogier van Meggelen
The code works great! is it also possible to save the different tabs as a pdf? I tried but the i can't get it to work :)
2016-08-18 11:43 Reply Reply with quote Quote
Permalink 0 Rogier van Meggelen
The code works great. Can you also use this to save the tabs as pdf files.
It can't figure this out :)
2016-08-18 11:44 Reply Reply with quote Quote
Permalink 0 shanojirao
Hi All,

I need a help from you all,

I've to share the reports to multiple team depending on the brands they work for.

Eg: brand name:
Apple
Samsung
and many more around 60 brands.

there will be a sales dump.

If I want to run a macro, and spilt into multiple excel files what would be the procedure.

Kindly help me.

Thanks in Advance.

Regards,
Shan
2016-09-20 12:23 Reply Reply with quote Quote
Permalink 0 Bakhty
VBA script worked like a charm, got my 80+ new excel files
2016-09-26 02:59 Reply Reply with quote Quote
Permalink 0 Snehalata Gupta
Hello all,

I need to reciprocate the same thing.

there are lots of excel sheets and i want it te 1st sheet of it in single work book,
Please Help!!!!
2016-10-10 10:23 Reply Reply with quote Quote
Permalink 0 Jignesh S Thakrar
Hi

I'm have master file were data consist for multiple city would like know is their any macros or any formula which keeps this orignal file and also create & update new workbook city area wise
2016-10-16 08:00 Reply Reply with quote Quote
Permalink 0 sunil
Used this and it worked. Just what I needed. Thanks
2016-11-07 16:27 Reply Reply with quote Quote
Permalink 0 Brad
IT SHOULD BE NOTED THAT THE VBA MACRO ABOVE WON'T WORK IF YOU HAVE A HIDDEN EXCEL SHEET, OR A "VERY-HIDDEN" EXCEL SHEET.

YOU MAY NOT BE AWARE THAT YOU HAVE A "VERY-HIDDEN" EXCEL SHEET. TO CHECK, HIT ALT+F11 TO OPEN MICROSOFT VISUAL BASIC (MACRO EDITOR). CLICK ON EACH WORKSHEET UNDER THE MICROSOFT EXCEL OBJECTS AND MAKE SURE THAT THE "Visible" ATTRIBUTE IN THE "Properties" SECTION SHOWS "-1 = xlSheetVisible" . IF YOU SPIT AN EXCEL FILE OUT OF ORACLE, FOR EXAMPLE, YOU'LL SEE A WORKSHEET NAMED "BneLog" with visibility = "2 - xlSheetVeryHidd en". This took me forever to figure out, lol. Hope it helps!
2016-11-09 00:44 Reply Reply with quote Quote
Permalink +1 Brad
The macro should be updated to include an unhide-all-work sheets piece (including the very-hidden worksheets):

Sub UnhideAllSheets()
'Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

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
2016-11-09 01:16 Reply Reply with quote Quote
Permalink 0 DS
This VBA code worked perfectly. Thank you for sharing your wealth of knowledge.
2016-11-14 16:15 Reply Reply with quote Quote
Permalink 0 Anjali
Hi, i have one quarry i.e. i have 15000 lines of data in one excel sheet and i want to split that data into multiple excel files where as each file contains 99 lines of data. Anyone can help me. Please share your thoughts.....
2016-12-01 04:08 Reply Reply with quote Quote
Permalink 0 Jessica P.
Thank you very much for this.Saved me weeks of sweat...
 
Regards:Jessica
Active consumer on custome rso
2016-12-18 00:04 Reply Reply with quote Quote
Permalink 0 Tomm
2 questions.

1. How can you adjust the code the choose the file path of where it is saved? As opposed to just saving wherever the original file is.

2. How do you change the code to choose what column it creates the tabs from (i.e. in this code it is reading out of column A). I want to be able to choose which column it reads off if. Thanks!
2017-01-11 14:29 Reply Reply with quote Quote
Permalink 0 Nikk
Thank you so much! It worked after I unhid the rest of the workbook. This is awesome!
2017-01-26 21:23 Reply Reply with quote Quote
Permalink 0 Karissa Brandhagen
How can I get this .xlsx file to save as .csv when split?
2017-01-27 00:16 Reply Reply with quote Quote
Permalink 0 adnan
Hi
The coding works well but I want the header also to come on each and every sheet. As it's skipped the header in all data...
2017-01-27 11:14 Reply Reply with quote Quote

Add comment


Security code
Refresh