Hi Guys
I found this on another forum and it works great
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Const YesCol As String = "O" '<- Your 'completed' column
Const HeaderRow As Long = 7 '<- 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
Is there any way at clear contents that it leaves the first two columns and deletes the remaining?
At the moment it clears the whole archived selection including my location tags.
Thanks
Andy
Bookmarks