I need to take a large workbook that has several worksheets, and save each
worksheet as an individual workbook, maintaining links if possible. I know I
can do this manually, but would like to do it as automatically as possible.

right click on the tab, move or copy, select new book off dropdown, check
make copy if you want to preserve the original workbook

"Kueck" wrote:

I need to take a large workbook that has several worksheets, and save each
worksheet as an individual workbook, maintaining links if possible. I know I
can do this manually, but would like to do it as automatically as possible.

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

Will retain links.


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 13:43:02 -0700, Kueck
wrote:

I need to take a large workbook that has several worksheets, and save each
worksheet as an individual workbook, maintaining links if possible. I know I
can do this manually, but would like to do it as automatically as possible.

Sounds promising - what do I do with this?

"Gord Dibben" wrote:

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

Will retain links.


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 13:43:02 -0700, Kueck
wrote:

I need to take a large workbook that has several worksheets, and save each
worksheet as an individual workbook, maintaining links if possible. I know I
can do this manually, but would like to do it as automatically as possible.

I figured out what to do with it. Now, how can I instruct it to save as .xls
rather than .xlsx

"Gord Dibben" wrote:

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

Will retain links.


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 13:43:02 -0700, Kueck
wrote:

I need to take a large workbook that has several worksheets, and save each
worksheet as an individual workbook, maintaining links if possible. I know I
can do this manually, but would like to do it as automatically as possible.

I don't run Excel 2007 so don't know the exact syntax for saving from one
version to the other.

Try recording a macro whilst doing a FileSaveAsFileType and choosing the
*.xls version.


Gord

On Fri, 14 Nov 2008 14:16:05 -0800, Kueck
wrote:

I figured out what to do with it. Now, how can I instruct it to save as .xls
rather than .xlsx

"Gord Dibben" wrote:

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

Will retain links.


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 13:43:02 -0700, Kueck
wrote:

I need to take a large workbook that has several worksheets, and save each
worksheet as an individual workbook, maintaining links if possible. I know I
can do this manually, but would like to do it as automatically as possible.