sorting in alphabetical order not working when a combo box is linked to a named range

[dpaton05]

I have a spreadsheet that works fine but I have just been told the combo box that pulls a named range from another sheet needs to sort in alphabetical order.

I run this procedure to add a young person

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String
    'Assign the name in D5 of the Tracker sheet to the variable newyp
    newyp = Tracker.Cells(5, 4)
    'Offset from the bottom of the list of YP by 1 to add the newyp to the list
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
        'Call CreateWB sub and send the variable newyp to it
        Call CreateWB(newyp)
    'Define a new named range for tblYPNames by resizing the current range by _
    one row down
    ThisWorkbook.Names.Add Name:="tblYPNames", _
    RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
    'Sort the YP names list in alphabetical order
    Call Sort
Application.DisplayAlerts = True
End Sub

It works fine until it gets down to the sort sub

I get the error method range of object worksheet failed with this line highlighted

Sub Sort()
YP.Range("A2", Range("A2").End(xlUp)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
End Sub

The combo box has a listfillrange of tblYPNames.

Can someone help me please?

 

[Michael M]

Try using

YP.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo

 

[dpaton05]

Thanks for the reply Michael. That works fine but I want it to sort the names on the sheet with the code name YP.

I enter YP before range and I get the error, the sort reference is not valid.

It highlights

YP.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo

 

[MARK858]

You need to add YP in front the 2 other places it says Range in the code or else it will error if YP is not the ActiveSheet.

 

[Michael M]

As @MARK858 stated, if the YP sheet isn't the active sheet you will need

YP.Range("A2:A" & YP.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=YP.Range("A2"), Order1:=xlAscending, Header:=xlNo

 

[dpaton05]

Thanks guys, totally missed that.

This appears to be working, is this the correct code with a header in A1?

YP.Range("A1:A" & YP.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=YP.Range("A1"), Order1:=xlAscending, Header:=xlYes

 

[Michael M]

Did you try it ???

 

[dpaton05]

Yes, I did. It appears to be working. I guess if it is working, it is working.

Thanks guys for your help.