Turning Tabs into Files

Upton333

New Member
Joined
Dec 10, 2003
Messages
15
I expect this is very simple once you know how and I think I am 90% of the way there but can't quite make the magic happen!

I have a file with around 50 tabs that I want to make into individual files. I want to save them all in the same drive (Say C\Documents\Properties) and I want to name each file after its tab name.

Any assistance with a macro to do this will be greatly appreciated.

Many thanks
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Something like:

Code:
Sub test()
Dim myWS As Worksheet
For Each myWS In ThisWorkbook.Worksheets
myWS.Copy
With ActiveWorkbook
    .SaveAs ThisWorkbook.Path & "\" & myWS.Name & ".xls"
    .Close
End With
Next myWS
End Sub

Will split the file into different workbooks for each sheet in the same directory as the master workbook is saved.

Change:

Code:
.SaveAs ThisWorkbook.Path & "\" & myWS.Name & ".xls"

To

Code:
.SaveAs "C:\Documents\Properties\" & myWS.Name & ".xls"

If you want to have the master workbook elsewhere.

Hope it helps,

Dom
 
Last edited:

Upton333

New Member
Joined
Dec 10, 2003
Messages
15
Exactly like that. That has saved my secretary hours of cut and save as.

From her and me, many thanks.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,128,120
Messages
5,628,803
Members
416,340
Latest member
PJB1102

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