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
        ws.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs strFilename
        wbNew.Close
    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.

Sources:

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

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

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

    Fantastic – thanks heaps mate!

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

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

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

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

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

    Boom! Just saved me hours of work! Thanks

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

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

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

    • #9 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 :(

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

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

      • #11 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.

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

        THAAAAAAAANKS!!!!!!!!!

  9. #13 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.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: