Export Multiple Tabs in One file to Individual Files

lucky12341

Board Regular
Joined
Nov 4, 2005
Messages
121
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
This will do it for the active workbook.
Code:
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.
 
Upvote 0
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
 
Upvote 0
Oops, should be SaveAs not just Save.:oops:
 
Upvote 0
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 :biggrin:
 
Upvote 0

Forum statistics

Threads
1,217,317
Messages
6,135,822
Members
449,965
Latest member
Ckl43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top