I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by using
script. Can anyone help?

Here's how I would approach it:
Dim separate objects for the application, the ActiveWorkbook, a new
workbook, and worksheet.
Set the application object and the ActiveWorkbook object.
Then loop through the worksheets coolection of the ActiveWorkbook:
For Each "ws" in "awb".Worksheets
Select the worksheet and copy
Set the "newwb" object to a new workbook
Paste
"newwb".SaveAs (you'll have to set a string to a filename)
"newwb".Close
Next "ws"
"awb".Close DoNotSaveChanges

You might try walking through the copy, new workbook, paste, saveas, close
once with the macro recorder on. Then you can go back into it, add the
objects and the filename string, and put the loop in.

Ed

"Min" wrote in message
...
I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by
using
script. Can anyone help?

Here is some code to do that for you... It will not overwrite any files
without asking first. It uses the tab name for the file name. I have set the
default path as C:\. You can change that...

Private Const strPATH As String = "C:\"

Private Sub SaveSheets()
Dim wks As Worksheet

For Each wks In Worksheets
wks.Copy
ActiveWorkbook.SaveAs strPATH & wks.Name
ActiveWorkbook.Close
Next wks

End Sub


"Min" wrote:

I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by using
script. Can anyone help?

Okay - much simpler than mine! 8{ (But I'm used to that!)
But how do you get just the copied sheet into a new workbook? Won't
ActiveWorkbook.SaveAs save the whole file, not just the sheet as a new file?

Ed

"Jim Thomlinson" wrote in message
...
Here is some code to do that for you... It will not overwrite any files
without asking first. It uses the tab name for the file name. I have set
the
default path as C:\. You can change that...

Private Const strPATH As String = "C:\"

Private Sub SaveSheets()
Dim wks As Worksheet

For Each wks In Worksheets
wks.Copy
ActiveWorkbook.SaveAs strPATH & wks.Name
ActiveWorkbook.Close
Next wks

End Sub


"Min" wrote:

I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by
using
script. Can anyone help?

wks.copy creates a new workbook with just that sheet in it. Same as right
click on that tab - Create Copy - In new Workbook.

This is now the active workbook. You can refernce the original workbook as
thisworkbook (but we don't need to in this case).

Save and close the active workbook and go on to the next sheet.

With a little practice comes ability. With a lot of practice comes simplicity.

"Ed" wrote:

Okay - much simpler than mine! 8{ (But I'm used to that!)
But how do you get just the copied sheet into a new workbook? Won't
ActiveWorkbook.SaveAs save the whole file, not just the sheet as a new file?

Ed

"Jim Thomlinson" wrote in message
...
Here is some code to do that for you... It will not overwrite any files
without asking first. It uses the tab name for the file name. I have set
the
default path as C:\. You can change that...

Private Const strPATH As String = "C:\"

Private Sub SaveSheets()
Dim wks As Worksheet

For Each wks In Worksheets
wks.Copy
ActiveWorkbook.SaveAs strPATH & wks.Name
ActiveWorkbook.Close
Next wks

End Sub


"Min" wrote:

I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by
using
script. Can anyone help?

With a little practice comes ability. With a lot of practice comes
simplicity

That's a mouthful, Jim. I will remember that. Thanks.
Ed

"Jim Thomlinson" wrote in message
...
wks.copy creates a new workbook with just that sheet in it. Same as right
click on that tab - Create Copy - In new Workbook.

This is now the active workbook. You can refernce the original workbook as
thisworkbook (but we don't need to in this case).

Save and close the active workbook and go on to the next sheet.

..

"Ed" wrote:

Okay - much simpler than mine! 8{ (But I'm used to that!)
But how do you get just the copied sheet into a new workbook? Won't
ActiveWorkbook.SaveAs save the whole file, not just the sheet as a new
file?

Ed

"Jim Thomlinson" wrote in
message
...
Here is some code to do that for you... It will not overwrite any
files
without asking first. It uses the tab name for the file name. I have
set
the
default path as C:\. You can change that...

Private Const strPATH As String = "C:\"

Private Sub SaveSheets()
Dim wks As Worksheet

For Each wks In Worksheets
wks.Copy
ActiveWorkbook.SaveAs strPATH & wks.Name
ActiveWorkbook.Close
Next wks

End Sub


"Min" wrote:

I have a workbook that has 100 sheets. I want to save each sheet as
a
separate xls file. Even though I could save each by copying into
new
worksheet and save manually, I am looking for a way to automate it
by
using
script. Can anyone help?