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

[Guest]

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?

 

[Ed]

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

 

[Guest]

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

 

[Ed]

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

 

[Guest]

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.