Macro not activating

Question

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.&nbsp;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&nbsp;&nbsp;

hansvogelaar · Accepted Answer

Linzer&nbsp;
Do you update the data in the deviation masterlist sheet manually? If so, the code should work if it is placed in the worksheet module of that sheet (not in the worksheet module of the ranked masterlist sheet!)
But I'd prefer to sort the ranked masterlist sheet when it is activated:
Right-click the sheet tab of the ranked masterlist sheet.
Select View Code from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Activate()
    With Me.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, _
                Order:=xlDescending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=Range("F1"), SortOn:=xlSortOnValues, _
                Order:=xlDescending, DataOption:=xlSortTextAsNumbers
        .SetRange Range("B1").CurrentRegion
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Forum Discussion

Macro not activating

Share

Resources