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?