Run macro when cell changes - not working

[alexbat]

Hi,

quite new to VB. I try to run a script when any of the cells in a range changes with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:F5")) Is Nothing Then
    Application.ScreenUpdating = False
    Sheets("Overdue data").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("J27:Q27").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("H22").Select
    ActiveWorkbook.Worksheets("Overdue data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Overdue data").Sort.SortFields.Add Key:=Range( _
        "H20:H1314"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Overdue data").Sort
        .SetRange Range("B19:I1314")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B19:I3000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Range("D1:F4"), CopyToRange:=Range("J27:Q27"), Unique:=False
    Sheets("Overdues").Select
    ActiveWindow.SmallScroll Down:=-18
    Application.ScreenUpdating = True
    
End If
End Sub

I don't get it to work, it stops at line

Range("J27:Q27").Select


What am I doing wrong??

 

[Andrew Poulsom]

When code is in a Worksheet module, unqualified properties refer to the worksheet that contains the code rather than the active sheet as is the case for code in a General module. So you need:

ActiveSheet.Range("J27:Q27").Select

 

[alexbat]

Andrew,

the script works now, but not as I intended...

I put the code in the sheet "Dashboard" where the users can do some choices in cells B5:F5. These changes should trigg some sorting and filering in sheet "Overdue data".
It seems that the script performs actions within the sheet "Dashboard" and not "Overdue data" as I want it to.

 

[Andrew Poulsom]

Did you qualify all the Range references with the worksheet?