I am trying to make a simple macro function that will save ALL
worksheets in one spreadsheet file into a separate spreadsheet file.
(1 file for each worksheet.)

For example, if "MASTER.xls" has 50 worksheets, then the macro would
create the following:

worksheet1.xls
worksheet2.xls
..
..
worksheet50.xls

(The name of the file should be the name of the worksheet.)

I have created the following code, but it does not work. It does
create the first spreadsheet file correctly, but then it gives me the
following error:

Run-time error ‘9': Subscript out of range

When I do the debug, the yellow highlight is on for the
"Sheets(Sheet.name).Select" line.

I'm not sure what is wrong, because the MsgBox alert shows the correct
worksheet name...

Here is my current code:

Sub Macro_Svae()
'
' This macro will create a new workbook (spreadsheet file)
' for every sheet in the current workbook.
'
Dim worksheet_filepath As String
Dim worksheet_filename As String
Dim Sheet As Worksheet

worksheet_filepath = "C:\workfiles\andy"

' Do this for each worksheet in the workbook.
For Each Sheet In Worksheets

worksheet_filename = Sheet.name
MsgBox Sheet.name

Sheets(Sheet.name).Select
Sheets(Sheet.name).Copy

'ChDir worksheet_filepath
ActiveWorkbook.SaveAs FILENAME:= _
worksheet_filepath & "\" & worksheet_filename & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

'Dim Sheet As Worksheet

Next Sheet

End Sub

Try this one
Change the path yours

Sub test()
Dim a As Integer
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
ActiveWorkbook.SaveAs "C:\" & Sheets(1).Name & ".xls"
ActiveWorkbook.Close False
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Henri" wrote in message om...
I am trying to make a simple macro function that will save ALL
worksheets in one spreadsheet file into a separate spreadsheet file.
(1 file for each worksheet.)

For example, if "MASTER.xls" has 50 worksheets, then the macro would
create the following:

worksheet1.xls
worksheet2.xls
.
.
worksheet50.xls

(The name of the file should be the name of the worksheet.)

I have created the following code, but it does not work. It does
create the first spreadsheet file correctly, but then it gives me the
following error:

Run-time error '9': Subscript out of range

When I do the debug, the yellow highlight is on for the
"Sheets(Sheet.name).Select" line.

I'm not sure what is wrong, because the MsgBox alert shows the correct
worksheet name...

Here is my current code:

Sub Macro_Svae()
'
' This macro will create a new workbook (spreadsheet file)
' for every sheet in the current workbook.
'
Dim worksheet_filepath As String
Dim worksheet_filename As String
Dim Sheet As Worksheet

worksheet_filepath = "C:\workfiles\andy"

' Do this for each worksheet in the workbook.
For Each Sheet In Worksheets

worksheet_filename = Sheet.name
MsgBox Sheet.name

Sheets(Sheet.name).Select
Sheets(Sheet.name).Copy

'ChDir worksheet_filepath
ActiveWorkbook.SaveAs FILENAME:= _
worksheet_filepath & "\" & worksheet_filename & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

'Dim Sheet As Worksheet

Next Sheet

End Sub