Macro not activating
Hi, I’m a VBA beginner and I’m trying to set up a macro to auto-sort the contents of the sheet “ranked masterlist” when the database in “deviation masterlist” (which the ranked masterlist references) is updated. I’ve managed to cobble this code together from what I’ve seen online, but when I edit one of the cells in “deviation masterlist” at B1:B447, nothing happens. I know the sorting code works because I can trigger it with a button press, but presumably there’s a syntax issue in how I’ve added the condition at the start. If anyone could point to what I’m doing wrong, it would be much appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveWorkbook.Worksheets(“deviation masterlist”).Range(“C2:C455”)) Is Nothing Then
ActiveWorkbook.Worksheets(“ranked masterlist”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“ranked masterlist”).Sort.SortFields.Add2 Key:= _
Range(“B1:B447”), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets(“ranked masterlist”).Sort.SortFields.Add2 Key:= _
Range(“F1:F447”), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(“ranked masterlist”).Sort
.SetRange Range(“B1:G447”)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
Hi, I’m a VBA beginner and I’m trying to set up a macro to auto-sort the contents of the sheet “ranked masterlist” when the database in “deviation masterlist” (which the ranked masterlist references) is updated. I’ve managed to cobble this code together from what I’ve seen online, but when I edit one of the cells in “deviation masterlist” at B1:B447, nothing happens. I know the sorting code works because I can trigger it with a button press, but presumably there’s a syntax issue in how I’ve added the condition at the start. If anyone could point to what I’m doing wrong, it would be much appreciated. Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveWorkbook.Worksheets(“deviation masterlist”).Range(“C2:C455”)) Is Nothing Then
ActiveWorkbook.Worksheets(“ranked masterlist”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“ranked masterlist”).Sort.SortFields.Add2 Key:= _
Range(“B1:B447”), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets(“ranked masterlist”).Sort.SortFields.Add2 Key:= _
Range(“F1:F447”), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(“ranked masterlist”).Sort
.SetRange Range(“B1:G447”)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub Read More
