save the ind workshts in a workbk as separate workbks automatical


K

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.
 
Ad

Advertisements

D

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
 
G

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
 
K

Kueck

Sounds promising - what do I do with this?

Gord Dibben said:
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
 
K

Kueck

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

Advertisements

G

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top