Split Excel Worksheets to Individual Files

Earlier today I found myself putting a lot of data into a workbook with multiple worksheets within it, what I wanted to do was create an individual Excel file for each of the worksheets – perhaps you have been in a position like this yourself?

The good news, it’s very easy to do.  I came across the VBA script below and don’t take any credit for it – follow my short instructions below and you’ll find this is a very straight forward procedure.  Note this is for Microsoft Excel and does not require any third party software.

  1. Create a new folder and save your master document in there.
  2. With the document open, name each sheet the name you’d like each file to be called.
  3. Once ready, hold the ALT key on your keyboard and press F11.
  4. On the Visual Basic window, select Insert > Module.
  5. Paste the VBA script shown below.
  6. Press the F5 key on your keyboard.
  7. Job done!
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”
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub

b222d-vba


Note on the fourth line from the bottom, change the .xls to .xlsx if you are working with a newer Excel format.
When I have some time, I’ll create a video demonstrating this.
About 

23 Comments

  1. Nelson Cruz

    4th April 2014 - 16:58
    Reply

    Hi,
    This was very helpful information, Thank you.
    Do you know how i can solve this same issue in Excel 2011 for Mac OSX?

    Thanks,
    Nelson

  2. Dena Paolucci

    13th May 2014 - 19:59
    Reply

    This was great – you just helped me create 56 files out of one workbook. Thank you!

    The only glitch I ran into was that formulas from the original workbook didn't copy over into the new files. Is there something I can add to the VBA script that will keep the formulas intact?

    Thanks,
    Dena

    • Mili

      28th January 2017 - 17:16
      Reply

      Same question here. I don’t see where he provided an answer 🙁

  3. Anonymous

    6th September 2014 - 05:01
    Reply

    Nice

  4. Anonymous

    26th September 2014 - 16:30
    Reply

    Doesn't work because of syntax error at : Filename:=MyPath & “” & sht.Name & “.xlsx”

  5. Anonymous

    8th January 2015 - 19:21
    Reply

    Hi, Chris — This worked like a charm, I can't thank you enough. Carolyn

  6. Anonymous

    16th January 2015 - 18:27
    Reply

    Awesome! I just stripped 90 tabs to 90 files. Appreciated!

    Mark

  7. 9thirty5

    25th January 2015 - 09:02
    Reply

    it works great except the sheets can't be protected. how can i make it work on protected sheets? thanks! John

  8. Anonymous

    23rd April 2015 - 21:00
    Reply

    Thank you, Chris! I was having a “there's gotta be a way” moment, and found your video. Kathy

  9. Anonymous

    20th May 2015 - 11:39
    Reply

    Excellent. Managed to extract 152 sheets into different files. The only things I have found is that, upon opening the files, I get a message stating that the file is in a different format to the file extension. Verify that the file is not corrupted and is from a trusted source before opening.

  10. Shane Lalor

    26th August 2015 - 09:51
    Reply

    Thanks Chris – that worked perfectly. Saved me a lot of time.

  11. Emma

    13th October 2015 - 15:41
    Reply

    For Mac, I needed to switch the to / in the file name path. Then it ran perfectly!

  12. Jim Sanders

    11th August 2016 - 15:17
    Reply

    Change the quotation marks. Apparently when I copied over to my computer I got a different format quotation mark and spaces appeared between the . I retyped the quotation marks and eliminated the spaces. Worked after that.

  13. Bryan

    15th September 2016 - 19:36
    Reply

    On the Filename line I receive the error message Compile Error: Expected Expression and it highlights in blue the :=

    • Chris

      16th September 2016 - 12:52
      Reply

      Bryan, are you using Windows or Mac? If on a Mac you may need to change the to a /

  14. Hector Taylor

    22nd September 2016 - 03:23
    Reply

    I getting “Run-time error ’11’: Division by zero

    • Malik

      27th October 2016 - 11:09
      Reply

      I have same error !!

  15. Sherry

    23rd October 2016 - 23:28
    Reply

    I am getting this message, can anyone help?
    Compile error
    Must be first statement on the line
    When I press help it says below.
    Not all keywords can appear at the beginning of a line of code. This error has the following causes and solutions:

    •You preceded a Sub, Function, or Property statement with another statement on the same line. A Sub, Function, or Property statement must always be the first statement on any line in which it appears (unless preceded by the keyword Public, Private, or Static).

    •You preceded an End If, Else, or ElseIf statement with another statement on the same line. An End If, Else, or ElseIf (only when used in a block If structure) statement must always be the first statement on any line in which it appears.

    • Chris

      24th October 2016 - 11:11
      Reply

      Can you confirm your Excel version please Sherry? Example, Office 2013 on Windows. There are many reasons you may be getting the error message, let’s first confirm the VBA code is being received correctly – can you trying again using the following link for the source? http://pastebin.com/tCeF7XMR

  16. Bert89

    30th October 2016 - 15:35
    Reply

    Reading your website is big pleasure for me, it deserves to go
    viral, you need some initial traffic only.

    If you want to know how to get it search for: blackhatworren’s strategies

  17. Tasha91

    10th November 2016 - 11:05
    Reply

    I see your blog is in the same niche like my website. Do you allow guest posting?
    I can write interesting & unique posts for you. Let me know if you are interested.

  18. Patty

    3rd February 2017 - 17:56
    Reply

    I get a run-time error ’11’:

    and I really want to use this script!!

    When I debug, it highlights lines 8 and 9.

    I checked my script at the link you shared with Betty above. It’s just not working for me.

  19. Sarah

    17th May 2017 - 23:43
    Reply

    Just discovered this command and it would seriously save my life, but I keep seeing the following error –

    Run-time error ‘1004’:
    Copy method of Worksheet class failed

    Debug points to sht.Copy as the error. Any suggestions?

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use my site, you agree to the use of cookies. More Information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close