Kutools for Excel 22.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 13.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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
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.
    zirokl · 4 years ago
    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(without 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.
  • To post as a guest, your comment is unpublished.
    NG · 4 years ago
    you saved my day with this code!!
  • To post as a guest, your comment is unpublished.
    Ovi · 4 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Brian · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Sam · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Caroline · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    John Boyd · 5 years ago
    Good stuff! Thank you!
  • To post as a guest, your comment is unpublished.
    Maggie · 5 years ago
    I receive a Run time error '1004'. Is this due to me using a pivot table? Is there a way around this?
    • To post as a guest, your comment is unpublished.
      Shiva-India · 5 years ago
      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.
      • To post as a guest, your comment is unpublished.
        KY · 1 years ago
        THANK YOU! Finally I got this to work.
  • To post as a guest, your comment is unpublished.
    Fayyadh · 5 years ago
    Its not work
    Stuck at xWs.Copy
    what i suppose to do?

    Thanks :-)
    • To post as a guest, your comment is unpublished.
      Karen · 4 years ago
      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?
  • To post as a guest, your comment is unpublished.
    Andrew · 5 years ago
    I have to say I [b]LOVE[/b] 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?
  • To post as a guest, your comment is unpublished.
    s · 5 years ago
    Superb Code. Actually searching for this.
  • To post as a guest, your comment is unpublished.
    Krishna · 5 years ago
    It's really Useful. Thanks for sharing.
  • To post as a guest, your comment is unpublished.
    Yasir Arfat · 5 years ago
    Can you please tell me that how can i send mass emails to with personalized attachment in outlook..

    Regrads,
    Yasir
    • To post as a guest, your comment is unpublished.
      Sanjay · 5 years ago
      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.
  • To post as a guest, your comment is unpublished.
    nayeem khan · 5 years ago
    Dear Sir,

    Can your please send me details how coding to split s
    • To post as a guest, your comment is unpublished.
      Sanjay · 5 years ago
      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
      • To post as a guest, your comment is unpublished.
        Lisa · 3 years ago
        Can I save your module script to the PERSONAL Macro Workbook for future use?
      • To post as a guest, your comment is unpublished.
        Rony · 3 years ago
        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.
      • To post as a guest, your comment is unpublished.
        denise · 5 years ago
        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?
  • To post as a guest, your comment is unpublished.
    Sanjay · 5 years ago
    This code & the KUtools both shows an error after converting 222 files & after 222 files both stop working.
  • To post as a guest, your comment is unpublished.
    Suzi · 5 years ago
    Its Showing error in the line 8....
    xws cpy is error
    • To post as a guest, your comment is unpublished.
      Marie · 4 years ago
      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 :)
  • To post as a guest, your comment is unpublished.
    Lara · 5 years ago
    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?
  • To post as a guest, your comment is unpublished.
    revati · 6 years ago
    Used this and it worked. Just what I needed. Thanks
  • To post as a guest, your comment is unpublished.
    ADO · 6 years ago
    Great help!!! extremely easy and useful :-)
  • To post as a guest, your comment is unpublished.
    Jennifer · 6 years ago
    Very useful! Thanks for sharing
  • To post as a guest, your comment is unpublished.
    Dodger · 6 years ago
    VBA instructions were "spot on"! ... even for a novice!
    Worked great!
  • To post as a guest, your comment is unpublished.
    Frankie · 6 years ago
    Thank you! Saved me a lot of manual effort!
  • To post as a guest, your comment is unpublished.
    joy · 6 years ago
    Thank you method 1 was extremely useful
  • To post as a guest, your comment is unpublished.
    Starscor · 6 years ago
    Useful script, but i'm looking for further steps, to self-pick a critera from a excel and auto-populate into multiple files
  • To post as a guest, your comment is unpublished.
    RAVISHANKAR · 6 years ago
    Awesome script.It's worked for me like anything. Thanks.
  • To post as a guest, your comment is unpublished.
    Shatrughan singh · 6 years ago
    Really superb facility.., amazing...wow..
  • To post as a guest, your comment is unpublished.
    KP · 6 years ago
    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
  • To post as a guest, your comment is unpublished.
    terp · 6 years ago
    Awesome code - saved the day for me. Thank you for posting.
  • To post as a guest, your comment is unpublished.
    Chandrasekar T · 6 years ago
    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)
  • To post as a guest, your comment is unpublished.
    Daniel · 6 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Rebecca · 6 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Dhanasekar Sakthivel · 6 years ago
    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.

Feature Tutorials