Note: The other languages of the website are Google-translated. Back to English

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.

  1. Split a workbook to separate Excel files with copying and pasting
  2. Split a workbook to separate Excel Files with Move or Copy feature
  3. Split a workbook to separate Excel files with VBA code
  4. 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 is an easy-to-use way 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 Move or Copy feature

This method will introduce the Move or Copy feature to move or copy the selected sheets to a new workbook and save as a separate workbook. Please do as follows:

1. Select the sheets in the Sheet tab bar, right click, and select Move or Copy from the context menu. See screenshot:

Note: Holding Ctrl key, you can select multiple nonadjacent sheets with clicking them one by one in the Sheet tab bar; holding Shift key, you can select multiple adjacent sheets with clicking the first one and the last one in the Sheet tab bar.

2. In the Move or Copy dialog, select (new book) from the To book drop down list, check the Create a copy option, and click the OK button. See screenshot:

3. Now all selected sheets are copied to a new workbook. Click File > Save to save the new workbook.

Quickly split a workbook to separate Excel / PDF / TXT / CSV files in Excel

Normally we can split a workbook to individual Excel files with the Move or Copy feature in Excel. But Kutools for Excel's Split Workbook utility can help you easily split a workbook and save each worksheet as a separate PDF/TEXT/CSV file or workbook in Excel. Full Feature Free Trial 30-day!
ad split workbook excel

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. After installing Kutools for Excel, click Kutools Plus > 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) Check the Save a type option;
(3) From the Save as type drop down, choose one file type you want to split and save.
(4) Then click Split button.

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 Browse For Folder dialog, please specify a destination folder to save the split separate files, and click the OK button.

Now the checked 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), CSV files, TXT files, or PDF files as you need. You can set to skip all the blank and hidden worksheets. Have a Free Trial!


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


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (112)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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)
This comment was minimized by the moderator on the site
Awesome code - saved the day for me. Thank you for posting.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Really superb facility.., amazing...wow..
This comment was minimized by the moderator on the site
Awesome script.It's worked for me like anything. Thanks.
This comment was minimized by the moderator on the site
Useful script, but i'm looking for further steps, to self-pick a critera from a excel and auto-populate into multiple files
This comment was minimized by the moderator on the site
Thank you method 1 was extremely useful
This comment was minimized by the moderator on the site
Thank you! Saved me a lot of manual effort!
This comment was minimized by the moderator on the site
VBA instructions were "spot on"! ... even for a novice! Worked great!
This comment was minimized by the moderator on the site
Very useful! Thanks for sharing
This comment was minimized by the moderator on the site
Great help!!! extremely easy and useful :-)
This comment was minimized by the moderator on the site
Used this and it worked. Just what I needed. Thanks
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Its Showing error in the line 8.... xws cpy is error
This comment was minimized by the moderator on the site
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 :)
This comment was minimized by the moderator on the site
This code & the KUtools both shows an error after converting 222 files & after 222 files both stop working.
This comment was minimized by the moderator on the site
Dear Sir, Can your please send me details how coding to split s
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Can I save your module script to the PERSONAL Macro Workbook for future use?
This comment was minimized by the moderator on the site
Can you please tell me that how can i send mass emails to with personalized attachment in outlook.. Regrads, Yasir
This comment was minimized by the moderator on the site
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.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations