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

The line that starts .AutoFilter Field:=1 is throwing up an error.

Could anyone please advise where I am going wrong?

TIA!

CJG19

 

[Fluff]

Duplicate to: Auto Archive

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.