H
Henri
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
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