Archive from a table

[chris w]

Hi, I want to archive some items which are in a table when I set their status to "complete". In my test workbook it orks fine using this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range

Const YesCols As String = "M" '<- Your 'completed' columns

Set Changed = Intersect(Target, Columns(YesCols))
If Not Changed Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Intersect(ActiveSheet.UsedRange, Columns(YesCols))
.AutoFilter Field:=1, Criteria1:="=Completed"
With .Offset(1).EntireRow
.Copy Destination:=Sheets("ARCHIVE") _
.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
.AutoFilter
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

However, when I run this against the workbook which has the table in it I get "autofilter method of range class failed 1004" and despite whatever I try I can't make it work. Does anybody have any idea of how I can fix this?

 

[ricardodiazjimenez]

Hi, in what line you get the error?

 

[My Aswer Is This]

The script works for me.
Do you have a sheet named

ARCHIVE​

in the active workbook

Do you have any other auto sheet event scripts in this worksheet?