Save tabs to their own file

[krafty]

I have a large excel file with many tabs. I would like to be able to save
each tab to their own file with a simple process -- in as few steps as
possbile. Not one by one. Can anyone suggest a way to do this? What I
would also potentially like to do is have the file name it saves as be
defined as a single cell location that is in the same spot on every tab. For
example the content in cell C14 on every tab would be the file name...

Thanks!

 

[Toothsome Papa]

There may be some VB code that can automate that, but with regular excel
commands, you have to make a new workbook, copy the sheet to it, then save
it. one by one

 

[Gord Dibben]

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs FileName:=ActiveWorkbook.Path _
& "\" & w.Name & ".xlsx" '' & Range("C14").Value
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP