How do I save each sheet as a separate .xls file by using macro?

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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?
> >
> >
> >