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:

• http://www.mrexcel.com/forum/showthread.php?486738-Copying-Saving-Worksheets-from-a-large-workbook-into-separate-files