Move "Complete" Items to another sheet in workbook

**KathyWilk** · 11-18-2012, 01:53 PM

I need to complete an Actions list each week. I would like if any items marked "Complete" on the Actions sheet were automatically moved (cut) from the "Actions" sheet and put on the next row of the "Complete" sheet. I have tried to copy advice and failed. I attach a sample worksheet and would be very grateful for advice and help.
Thanks and regards
Kathy

**jaslake** · 11-18-2012, 02:12 PM

Hi Kathy

Please see attached which includes the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NR As Long
    If Not Target.Column = 5 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.Value = "Complete" Then
        Target.EntireRow.Copy
        With Sheets("Complete")
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            .Range("A" & NR).PasteSpecial
        End With
        Target.EntireRow.Delete
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Please note I've moved your Status List to a separate sheet and defined a Named Range called Status to populate Data Validation. Let me know of issues.

**AB33** · 11-18-2012, 02:19 PM

The attached code you have on the worksheet seems to work, so I do not know what is the issue.

Sub CopyCompl()
Dim ws As Worksheet, ws2 As Worksheet, lRow As Long

Set ws2 = Sheets("Complete")
Set ws = ActiveSheet

ws.AutoFilterMode = False
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("A2:I" & lRow).AutoFilter field:=5, Criteria1:="Complete"

If ws.Range("A" & Rows.Count).End(xlUp).Row > 2 Then
    ws.Range("A3:I" & lRow).SpecialCells(xlCellTypeVisible).Copy
    ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
End If
    Application.CutCopyMode = False
End Sub

**jaslake** · 11-18-2012, 02:26 PM

Hi Kathy

Lets try this again. The code you posted above is not required. In this attachment I've commented it out. Simply change a value in Column E to "Complete" and the line will move.