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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
 

lucky12341

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Oops, should be SaveAs not just Save.:oops:
 

lucky12341

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

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

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
Top