Archive from a table

chris w

New Member
Joined
Feb 8, 2006
Messages
1
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?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,490
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?
 
Last edited:

Forum statistics

Threads
1,086,202
Messages
5,388,378
Members
402,115
Latest member
ypompoms

Some videos you may like

This Week's Hot Topics

Top