Hi, I'm trying to do an auto sort whenever shortlisted admin worksheet is updated. When updates are made to Cell B, Cell Z will sort by descending.
But the following macro does not sort the list whenever new data are added to cell B. Following are the codes, i not sure where i did wrong.
B11:Z11 is the header
I have added this piece of macro to the worksheet(Shortlisted Admin) that needs to be sorted.
CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B12:Z200"))
If Not isect Is Nothing Then
With ActiveSheet
With .Sort.SortFields
.Clear
.Add Key:=Range("Z12"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
End With
With .Sort
.SetRange Range("A1:Z200")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End Sub
Is it possible to display first 6 results after sorting. Any help would be appreciated.
Bookmarks