Sorting without selecting in other worksheet?

[nigelandrewfoster]

This fails at the red line. I have several tables of ingredients in INGREDIENTS worksheet(where this code is stored), which is summarised in one long list in TABLES worksheet via formulas for the purpose of providing a listbox to select them, which has to be in alphabetical order. I want the table in TABLES to sort every time the corresponding entry on one of the INGREDIENTS tables is updated. I HAVE to copy the values first to column B as otherwise blank values are displayed first in the listbox, which I don't want. Am I right in saying that the problem is I cannot sort a range in a worksheet that is unselected? I want to get away from selecting a range at all in my programming, but:

     With ws.Range("B1:B280")
            .Selection.Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        End With

doesn't work either. Can you give me some suggestions? There has to be an easier way than this...? And one that works!

Private Sub worksheet_change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Worksheets("TABLES")
    If Not Intersect(Target, Range("j5:al96")) Is Nothing Then
        ws.Range("A1:A280").Copy
        ws.Range("B1").PasteSpecial Paste:=xlPasteValues
        ws.Range("B1:B280").Select
        [COLOR="Red"]Selection.Sort Key1:=ws.Range("B2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal[/COLOR]
    End If
End Sub

 

[Norie]

Try removing .Selection in the first code.

 

[nigelandrewfoster]

Thanks. That was a typo, actually.
This comes up with SORT RANGE INVALID:

Private Sub worksheet_change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Worksheets("TABLES")
    If Not Intersect(Target, Range("j5:al96")) Is Nothing Then
        ws.Range("A1:A280").Copy
        ws.Range("B1").PasteSpecial Paste:=xlPasteValues
        With ws.Range("B1:B280")
            .Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        End With
    End If
End Sub

 

[Norie]

Remove .Range("B1:B280") from the With

With ws
     .Range("B1:B280").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With

As far as I can see that should work, though I'm not 100% sure.

I can't see anything else that might have caused the error, but perhaps I've missed something.

 

[nigelandrewfoster]

It works! You are a gentleman and a scholar!