AutoSort based on 2 columns. Auto refresh without clicking on data

[Declamatory]

Hi Folks,

I have some code that sorts data in range S2:Z10. The data is sorted first by column Y then by column U.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    
        Set Rng = Intersect(Target, Range("Y1:Y10"))
        If Rng Is Nothing Then Exit Sub
        
        Rng.Sort Key1:=Range("Y2"), Order1:=xlAscending, _
                 Key2:=Range("U2"), Order2:=xlAscending, _
                 Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                 Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

End Sub

The problem is that all of the data in that range is pulled through from other worksheets. This means that the data changes but it doesn't sort it until I double click on a cell in the range and hit enter. Is there anyway to get the range to auto sort when the data in the range changes even though no it actually clicking on any cell in the range and hitting enter.

Thanks in advance.

Dec

[ppgab]

Hi Folks,

I have some code that sorts data in range S2:Z10. The data is sorted first by column Y then by column U.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    
        Set Rng = Intersect(Target, Range("Y1:Y10"))
        If Rng Is Nothing Then Exit Sub
        
        Rng.Sort Key1:=Range("Y2"), Order1:=xlAscending, _
                 Key2:=Range("U2"), Order2:=xlAscending, _
                 Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
                 Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

End Sub

The problem is that all of the data in that range is pulled through from other worksheets. This means that the data changes but it doesn't sort it until I double click on a cell in the range and hit enter. Is there anyway to get the range to auto sort when the data in the range changes even though no it actually clicking on any cell in the range and hitting enter.

Thanks in advance.

Dec

You need to provide more info, there's no way to visualize what you're describing

[Declamatory]

Sorry ppgab,

We have a team that plays in a league. The players play singles games and doubles games. Players are ranked according to their singles results.

The data below is in columns S to Z in a sheet called results. The data is sorted by the singles rank in column Y. The data in column Y is arrived at by looking at column T first and then column U (The % stats of wins vs losses).

Each player has their own worskheet in the workbook with their stats in. This information feeds into Range S2 to Z10 in the results worksheet i.e. nobody types into the range. It is updated automatically.

The vba code was intended to sort the data in the range into Singles Rank order when the data is column Y was updated. The vba code does work but only when I double click in one of the cells in the range and hit enter. Because nobody physically types anything into the range it isn't sorting when the data in the range is updated because of new information being inout to each players worksheet.

I was looking to see if there was a way for the sort to happen automatically.

Thanks

Dec

[ppgab]

Ah ok, now I understand, it would be easier if you could attach the worksheet for me to test.

But here are some things you can try :

VBA codes to force update (try one at a time at the end of your macro)

Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild
ActiveWorkbook.RefreshAll
DoEvents

Or, depending on your Excel knowledge, instead make a pivot table that will rank the players, and put this at the end of the macro to refresh the pivot table

Set pt = ActiveSheet.PivotTables("MyPivot")

    pt.RefreshTable

Sorting a regular table through VBA tends to be problematic