Auto Archive

[CJG19]

Good Morning,

I am trying to get rows on my spreadsheet to auto-archive when a column is changed to 'yes'. I have managed to adapt a macro from this forum, however it is getting to the code in red below and failing:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Changed As Range
   
    Const YesCol As String = "P" '<- Your 'Yes' column
   
    Set Changed = Intersect(Target, Columns(YesCol))
    If Not Changed Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
            .AutoFilter Field:=1, Criteria1:="=YES"
            With .Offset(1).EntireRow
                .Copy Destination:=Sheets("Archived P3 2022 onwards") _
                    .Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Delete
            End With
            .AutoFilter
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Could anyone please advise where I am going wrong?

TIA!

CJG19