How to save multiple tabs in Excel as separate files

Excel save worksheets as separate filesjen3ral10 years agoI have an excel file that has about 100 worksheets in it. It was actually an export of a bunch of information. I didnt re

How to save multiple tabs in Excel as separate files

Excel  save worksheets as separate filesjen3ral10 years ago

I have an excel file that has about 100 worksheets in it. It was actually an export of a bunch of information. I didnt 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 dont 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

Share this:

  • HOW TO: Play DVDs on your Wii
  • August 1, 2009
  • In "homebrew channel"
  • Batch script to delete temp files
  • June 10, 2010
  • In "Tweaks"
  • Vista  The dependency service or group failed to start
  • October 29, 2008
  • In "Windows"Categories: Microsoft OfficeTags: Excel 2010Leave a Comment

Video liên quan