Save tabs to their own file
 

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!

Save tabs to their own file
 

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

"krafty" wrote:

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!

Save tabs to their own file
 

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP

On Wed, 10 Mar 2010 11:25:01 -0800, krafty
wrote:

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!