Export Multiple Tabs in One file to Individual Files

[lucky12341]

I have quite a few excel files that have lots of multiple tabs in them and I'd like to be able to export the tabs into individual files and have that export just name the individual files by the tab name that it's exported. Is there an easy way to do this? It seems like it should be really simple but I've been unable to find any freeware or software that has this specific ability. Any help on this would be very appreciated.

Thank You,
TM

 

[Norie]

This will do it for the active workbook.

Dim wb As Workbook
Dim ws As Worksheet
For Each ws in ThisWorkbook.Worksheets
      ws.Copy
      Set wbNew = ActiveWorkbook
      wbNew.Save ThisWorkbook.Path & "\" & ws.Name
      wbNew.Close
Next ws

Note this doesn't have any error checking eg for invalid filenames, and if you wanted to do this for multiple workbooks we would need more information.

 

[lucky12341]

Code giving run time error

It's making the workbook and making the sheet but it's coming up with the following error: Run-time error '450': Wrong number of arguments or invalid property assignment. If I hit 'debug' it highlights the following code line: wbNew.Save ThisWorkbook.Path & "\" & ws.Name

Suggestions? The file I'm testing this on has 50 or tabs in it and I'd love it if the code can break out each tab and name the new file whatever that tab name is.

I have a main folder that has 81 excel files. Each of those files can have 1 tab in it or as many as 50+ tabs in it. My goal is to take these excel files apart.

Thank You for your help.
TM

 

[Norie]

Oops, should be SaveAs not just Save.

 

[lucky12341]

Ha! It's working beautifully!!!! Thank you so much, I can't tell you how much I appreciate this.

Take Care and have a good one.
TM