save the ind workshts in a workbk as separate workbks automatical

[Kueck]

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.

 

[dlw]

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

 

[Gord Dibben]

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

 

[Kueck]

Sounds promising - what do I do with this?

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

 

[Kueck]

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

 

[Gord Dibben]

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 File>SaveAs>FileType and choosing the
*.xls version.


Gord