MrExcel Publishing
Your One Stop for Excel Tips & Solutions

run time error'9':


Posted by Peter m on June 23, 2001 7:58 PM


the following macro produces this error
run-time error '9': Subscript out of range

what needs to be changed in order to get
this macro to work properly?

Sub insertnewsheetmacro()
'
' insertnewsheetmacro Macro
' Macro recorded 06/23/2001 by Peter M
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Sheets("WeekEnd 27.03.00").Copy After:=Sheets(1)
Sheets("WeekEnd 27.03.00(2)").Select
Sheets("WeekEnd 27.03.00(2)").Name = InputBox("What is the name of the new sheet?")

End Sub


Posted by Ivan F Moala on June 23, 2001 8:42 PM

This error usually indicates that one of the
named sheets doesn't exist as given in your
Named sheets routine. Not sure BUT I would guess
that it is Sheets("WeekEnd 27.03.00(2)")
If you are trying to create another copy of a sheet then I would suggest something like......
Note: 1) The error handling
2) There is a limit in adding extra sheets
excel97....have a look @ http://support.microsoft.com/support/kb/articles/Q177/6/34.ASP?LN=EN-US&SD=gn&FR=0&qry=adding%20new%20sheets&rnk=12&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

For a work around IF you will be adding more sheets then 300 ?


Ivan

Sub CreatNewSheet()
Dim Sh As Worksheet
Dim Cont As String

Sheets("WeekEnd 27.03.00").Copy After:=Sheets(1)

Add:
On Error Resume Next
ActiveSheet.Name = InputBox("What is the name of the new sheet?")
If Err.Number <> 0 Then GoTo ErrAdd

Exit Sub
ErrAdd:
Cont = MsgBox("Error = " & Err.Number & " Discription:= " & Err.Description & vbLf & _
"Continue ?", vbYesNo)
If Cont = vbYes Then Err.Clear: GoTo Add

End Sub

the following macro produces this error run-time error '9': Subscript out of range this macro to work properly?

Sub insertnewsheetmacro() ' ' insertnewsheetmacro Macro ' Macro recorded 06/23/2001 by Peter M ' ' Keyboard Shortcut: Ctrl+Shift+I ' Sheets("WeekEnd 27.03.00").Copy After:=Sheets(1) Sheets("WeekEnd 27.03.00(2)").Select Sheets("WeekEnd 27.03.00(2)").Name = InputBox("What is the name of the new sheet?") End Sub