Run Time Error 9

[Bankonit9]

Could anyone please explain why would I get a Run Time Error 9 with
this code?

Sub submit_roster()
Dim myName As String
Dim myFile As String
Dim myWeek As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

myFile = ActiveWorkbook.Name
myName = ActiveSheet.Name
myDate = Range("C5").Value
myName = myName & ". " & myDate
If Range("C5").Value = "" Then
MsgBox ("Pick a week to submit from the dropdown list.")
Range("C5").Select
Else
Range("C10:C192").Select
ActiveSheet.Unprotect Password:="richmond"
Selection.locked = True
ActiveSheet.Protect Password:="richmond"
Range("C5").Select
myCheck = vbIgnore
myCheck = MsgBox("Your " & myDate & " roster is now saved.
Are you ready to finalize by sending it to the league?", vbYesNo)
If myCheck = vbYes Then

Workbooks.Add
Sheets(Array("Sheet2", "Sheet3")).Select
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.SaveAs myName & ".xls"

Windows(myFile).Activate
Cells.Copy
Windows(myName).Activate
ActiveSheet.Paste

ActiveSheet.Name = Range("C5").Value & " Roster
Submission"
Range("C5").Select
Selection.locked = True
ActiveSheet.Protect Password:="richmond"
ActiveWorkbook.Save

MsgBox ("The file '" & myName & "' has been created and
saved in " & ActiveWorkbook.Path & ". Please attach it in an email
and send it to the league distribution list.")
Else
MsgBox ("Your submission file was not created.")
Range("C5").Select
End If
End If

Application.ScreenUpdating = True

End Sub

 

[Tom Ogilvy]

sure,
when you do

myName = myName & ". " & myDate

myname has a value like "abcd.xls"

whatever you append to it will be after the .xls

so myname will be like "abcd.xls.20040831"

you need to do

myName = left(myname,len(myname)-4) & mydate & ".xls"

then change
ActiveWorkbook.SaveAs myName & ".xls"
to
ActiveWorkbook.SaveAs myName

 

[DH]

Try this

Dim mydate As Long
Dim mycheck As Integer

Dave

 

[Benjamin Kochanski]

Thank you for your reply Tom.

One question.

If I set myName to the activesheet name, why would it end in .xls?

Wouldnt that only be the case if i set myName to the workbooks name, and
not the sheet?

 

[Tom Ogilvy]

You are correct - it was the variable above that was being assigned to the
workbook name. Trying to scroll through your code in a small window,
looking for assignments that might raise such an error . . . well, led to an
incorrect conclusion. You haven't said what line is highlighted when you
get the error. When you get the error, query the value of the argument to
the collection and see if it matches anything. A subscript out of range
error is usually caused by a name, used as an index to a collection, which
doesn't match any members of the collection. Such as the workbooks
collection, worksheets collection and so forth. You should examine such
variables as myName and the others that you are using as arguments to index
to the collection.

 

[Benjamin Kochanski]

Thanks Dave.

I somehow forgot to Dim those guys...wouldnt I want to Dim myCheck as a
vbMsgBoxResult, rather than as an integer?