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.
- Go to the developer tab and click on the visual basic button on the left side.
- Once the window opens go to the insert menu and select module.
- 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.