Auto sort

[stewy69]

hi, i have a range of cells that are referencing from another sheet and so have this code in them

=LOOKUP(9.99999999999999E+307,Results!F25:IV25)

to display a sheet like this


Latest Operations Director Regional Director Site


99 David Jenkins Mike Sawyer 4D SW (Sussex)
98 David Jenkins Mike Sawyer 4D SW (Kent)
97 David Jenkins Mike Sawyer TW: Earlswood STW
87 David Jenkins Mike Sawyer 4D Average
96 David Jenkins Mike Sawyer AWE Aldermaston
95 David Jenkins Chris Tyerman Malton Oil Storage
93 David Jenkins Chris Tyerman Swaledale
92 David Jenkins Chris Tyerman Staithes Lane
91 David Jenkins Chris Tyerman St Andrews Quay
1 David Jenkins Chris Tyerman Naburn SAS

where the latest column is where that code is.

i want the latest column to auto update when i change the values on the other sheet but the code i have tried using does not seem to work which i think is because of there being code in the cells and not just values.

This is what i have used before

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Set the target Range
Set rng = Range("B:B")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
ActiveSheet.UsedRange.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


thanks

Stuart

 

[BrianB]

I think this will work OK instead of what is there (NB. the sheet will not be the Active one).

Private Sub Worksheet_Calculate()
    Worksheets("Sheet1").UsedRange.Sort Key1:=Range("B1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[stewy69]

thanks brian that worked a treat, needed to move a bit of data around first but did exactly what i needed thanks