21 May 2013

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.

34 comments:

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

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

    ReplyDelete
  3. Doesn't work because of syntax error at : Filename:=MyPath & "" & sht.Name & ".xlsx"

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

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

    Mark

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

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

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

    ReplyDelete
  9. Thanks Chris - that worked perfectly. Saved me a lot of time.

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

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

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

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

    ReplyDelete
  14. I getting "Run-time error '11': Division by zero

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

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

    ReplyDelete
  17. I have same error !!

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

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

    ReplyDelete
  20. Same question here. I don't see where he provided an answer :(

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

    ReplyDelete
  22. 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?

    ReplyDelete
  23. Chris,

    On running the command, I am getting a compile error on the file path saying Expected:expression

    Any guidance on what I could do

    ReplyDelete
  24. Hi Chris,

    Echoing some of the comments below:
    I am using windows and I keep getting a syntax error for:
    Filename:=MyPath & “\” & sht.Name & “.xls”
    It keep highlighting the colon after file name, any ideas?

    Thanks

    ReplyDelete
  25. I am getting the same error with sintex Filename:=MyPath & “\” & sht.Name & “.xls”. could you help how to correct this?

    ReplyDelete
  26. I'm Sad... I get the run time error 11 division by zero. I have no idea what that means. Can you please help when you can. I placed the x in the xlsx. then I select debug and it highlight the two lines below. i'm using excel 2016 64bit

    ActiveWorkbook.SaveAs _
    Filename:=MyPath & “ \ ” & sht.Name & “.xlsx”

    ReplyDelete
  27. ABHISHEK GUPTA15 June 2018 at 09:54

    Try this:
    I had the same issue...changed the FIlename line to the following:
    Filename:=ActiveSheet.Name & ".xlsx"

    ReplyDelete
  28. It works with the latest Office 365 version if you fix the Filename line.

    Filename:=MyPath & "\" & sht.Name & ".xls"

    ReplyDelete
  29. Thanks Chris,

    works with your / suggestion for Mac and change to .xlsx

    ReplyDelete
  30. Mary Murray Taylor9 January 2019 at 18:13

    I see your reply stating the below. I am on Mac High Sierra. F5 doesn't work and I am not clear on what you mean below.
    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 /

    ReplyDelete
  31. This modification worked for me, however the tab names did not carry over the files were saved as sheet1, sheet2, etc.

    Filename = MyPath & "\" & sht.Name & ".xls"

    ReplyDelete
  32. Doesn't work because of syntax error at : Filename:=MyPath & "" & sht.Name & ".xlsx

    ReplyDelete
  33. This one works for Mac
    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

    ReplyDelete

Please use a Google Account to comment and check the box if you'd like notified of any responses. You can comment with just your name or anonymously if you'd prefer.