Hello,

I only receive the follow error when I do a "Save As" for my excel document.

Run-time error '1004':
Select Method of Range Class Failed


Why do I get this error when I use "Save As" and the debugger highlights the
following line in the below code?
Range("B9").Select

Is there a way to use the sheet name instead of "ActiveSheet"?



Private Sub cmbSort_Change()

Sort_Data ActiveSheet, "1234", cmbSort.Value

End Sub


Private Sub Sort_Data(sht As Worksheet, pwd As String, val As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With sht
If val = "Patient Name" Then
' Sort by Status by Patient Name
With Worksheets("PatientList")
.Range("PatientData").Sort Key1:=.Range("Status"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
Range("B9").Select

ElseIf val = "Discharge Date (No Status)" Then
' Sort by Discharge Date (No Status)
With Worksheets("PatientList")
.Range("PatientData").Sort Key1:=.Range("Discharge_Date"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
Range("B9").Select

Else
' Sort by Assigned Counselor Initials (No Status)
With Worksheets("PatientList")
.Range("PatientData").Sort Key1:=.Range("Couns_Assigned"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
Range("B9").Select

End If
End With

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan