Sort a named range when deactivating a worksheet

[The Dutchman]

I used a macro to come up with this code which doesn't work. The code is...

Private Sub Worksheet_Deactivate()
 
[COLOR=red][B][U]Range("staff1").Select
[/U][/B][/COLOR]    ActiveWorkbook.Worksheets("Set Up").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Set Up").Sort.SortFields.Add Key:=Range("staff1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Set Up").Sort
        .SetRange Range("I5:I14")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
End Sub

I get a "Select method of range class failed" error message. Do I have to list the worksheet related to the range?

Thx

 

[shg]

The red line should work fine if you're not on a chart sheet.

All the other range references, though, refer to the active sheet, not (necessarily) the sheet your'e trying to sort.

Edit: Now that you've changed changed your post ... staff1 has to be on the active sheet to be selected.

See Peter's code at http://www.mrexcel.com/forum/showpost.php?p=2559076&postcount=5

 

[mikerickson]

This is one reason that it is desirable to avoid Selecting. Since you are leaving the sheet, you can't select a range on that sheet.

    With ActiveWorkbook.Worksheets("Set Up")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("I5:I14"), SortOn:=xlSortOnValues _
            , Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("I5:I14")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

 

[The Dutchman]

Thx mikerickson.

Your code works great!