Excel – save worksheets as separate files

I have an excel file that has about 100 worksheets in it. It was actually an export of a bunch of information. I didn’t realize it would put it all into one huge excel file. I needed to save all the worksheets as their own files and definitely was not about to do this by hand. I found a forum where someone posted some visual basic code that would save the worksheets as their own files, with the worksheet names as their filenames, and save them in the same directory the original file is in – perfect.

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs strFilename
    Next ws
End Sub

If you don’t know how to run the VBA code in excel here are the step by step instructions.

You need to have the developer tab enabled first. To do that go to file -> options -> customize ribbon -> in the right column under main tabs check the box for developer.

  1. Go to the developer tab and click on the visual basic button on the left side.
  2. Once the window opens go to the insert menu and select module.
  3. Paste the code you want to run and to run it immediately you can press F5. Boom, it worked perfectly for me, now I have 100 individual files.


  1. #1 by Anonymous on March 23, 2017 - 1:46 PM


  2. #2 by Anand Kumar on December 17, 2015 - 11:33 PM


  3. #3 by yxkb on July 21, 2015 - 2:29 AM

    Perfect!! This really worked! Amazing how much time I can save with this little trick! Thanks a lot for sharering this!

  4. #4 by Anonymous on April 13, 2015 - 5:19 PM

    Worked a treat – thanks!!

  5. #5 by Chad W on February 17, 2015 - 6:39 AM

    I continue to get a “you cannot copy or move a group of sheets that contain a table” error message after I click run. Any suggestions? Roughly 100 sheets and a master on sheet 1 with a few hidden columns.

    Thanks in advance!

  6. #6 by zahid ali khan SWCC Al-Khobar on February 1, 2015 - 4:43 AM

    Thank you so much…….

  7. #7 by Cat1 on September 30, 2014 - 3:16 PM

    Wonderful – so useful, thank you.

  8. #8 by Anonymous on August 14, 2014 - 11:21 AM

    Works out of the box and super helpful. Thanks for your effort.

  9. #9 by Mariana on March 21, 2014 - 10:43 AM

    Hi, your post was really helpful. Just one question, how can I change the code to save the new worksheets Microsoft Office Excel 97-2003 Worksheet (.xls) and not in (.xlsx)? Thank you again

  10. #10 by Yun on February 8, 2014 - 5:05 PM

    Wow!!! Thank you very much! This is really very helpful!

  11. #11 by Lucas on January 31, 2014 - 7:22 AM

    thanks a lot, this saved me a lot of time

  12. #12 by Anonymous on June 26, 2013 - 8:42 AM

    Excellent … used this to reply to an email that insisted on having separate files for a workbook with only a half dozen tabs…

  13. #13 by Anonymous on June 5, 2013 - 8:05 AM

    WOW!!! The code and instructions to run the code (not a VB guy). Perfect in every detail, Thanks

  14. #14 by Anonymous on May 13, 2013 - 12:02 AM

    Fantastic – thanks heaps mate!

  15. #15 by Anonymous on March 4, 2013 - 9:37 PM

    Gr8 it`s a perfect option, it helps me a lot many thanks !!!!!!

  16. #16 by Anonymous on January 27, 2013 - 7:42 AM

    it is really a great options to know. thaks Buddy.

  17. #17 by Anonymous on November 27, 2012 - 4:40 AM

    Boom! Just saved me hours of work! Thanks

  18. #18 by sarah on October 17, 2012 - 8:45 AM

    Is there anyway to tweek the code to save as pdf?

    • #19 by Damian Lee Gomez on January 10, 2015 - 1:23 AM

      Hi Sarah, do you still need an answer to this?

  19. #20 by Hirayuki on September 9, 2012 - 8:02 PM

    Funny, this worked fine with one file last week, but now it’s halting at the copy line on another file. Any ideas what could be causing that? The sheets all have very plain names, and this is Excel 2010.

    • #21 by jen3ral on September 10, 2012 - 7:49 PM

      I have no idea. I was lucky to find this since I don’t know anything about visual basic. I used it a few times without any issues. Sorry 😦

    • #22 by salome on February 6, 2013 - 3:03 PM

      make sure you dont have any hidden sheets.. it will stop at this point.

      • #23 by Hirayuki on February 26, 2013 - 1:28 PM

        Thanks for the tip! I’ll remember to unhide any hidden sheets when I try it again.

      • #24 by Anonymous on March 20, 2013 - 7:25 AM


  20. #25 by Kyliente on June 29, 2012 - 1:04 PM

    Sweet! Thanks Jen! I was actually running into this issue a couple weeks ago and didn’t have the time to research it so I’m glad you did. I created a script that would convert the file to CSV, but was having issues with excel documents that had multiple worksheets. I can use this code to help me accomplish that. Thanks again.

  1. Split excel worksheets to individual files – zulkarnain hassan
  2. Split excel worksheets to individual files | Zulkarnain Hassan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: