Hi! First time poster and neophyte VBA user. I am attempting to copy and paste a row from worksheet "REGISTER" to the next available row in worksheet "ARCHIVE" whenever "Yes" is recorded in Column O of REGISTER. I have used the following code, taken from another forum, which manages to copy the row into ARCHIVE and deletes the old data from REGISTER, but when the next row has "Yes" recorded it overwrites the same row in ARCHIVE. I have tried many solutions, but each one keeps overwriting the same row in archive. Can someone please show me where I'm going wrong!?
Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range Const YesCol As String = "O" '<- Your 'completed' column Const HeaderRow As Long = 10 '<- Header row in main sheet Set Changed = Intersect(Target, Columns(YesCol)) If Not Changed Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Intersect(ActiveSheet.UsedRange, Columns(YesCol), Rows(HeaderRow & ":" & Rows.Count)) .AutoFilter Field:=1, Criteria1:="=Yes" With .Offset(1).EntireRow .Copy .Copy Destination:=Sheets("ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1) .ClearContents End With .AutoFilter End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
Last edited by a moderator: