Move Cells to Another Sheet based on Criteria

[ashleyditta]

I have searched and searched and searched, and tried modifying others and everything else I can think of and just can't get this figured out. Part of that is probably because this if my first attempt at anything like this..

But, I'm trying to move the entire row to another sheet ("Completed") once the word "Done" is in column "E" (on the first sheet "Names") and then have that row removed from the first sheet entirely. The rows added to the ("Completed") sheet will need to just keep on adding to whatever the next available row is so I can see all of them later.

Any help would be absolutely wonderful, as I'm ready to chuck my laptop 90% of the time I start working on this mess.

 

[MrKowz]

Welcome to the forums!

To make sure I understand your requirement:

On the sheet "Names", when any cell in column E = "Done", then copy that row to the bottom of the sheet "Completed" and delete the row from "Names". Do you want this to occur the moment the value in column E changes to "Done", or do you want to run the macro (or click a button) to batch-transfer multiple rows at once?

 

[ashleyditta]

Thank you! ?

Correct. "Completed" is on another sheet, I need everything that's "Done" moved over there. Instantly would be great! I didn't even realize that was an option.

 

[MrKowz]

Thank you! ��

Correct. "Completed" is on another sheet, I need everything that's "Done" moved over there. Instantly would be great! I didn't even realize that was an option.

The below code will monitor column E. When a value in column E changes to "Done" it will perform the task you are requesting.

Right click on the sheet tab for "Names" and choose "View Code". Copy/paste the below code into the window that opens up, and that should complete the installation of the code. Be sure to test this on a copy of your data.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR      As Long
If Not Intersect(Target, Range("E:E")) Is Nothing Then
    If Target.Value = "Done" Then
        'Copy row to "Completed" sheet and delete row
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        LR = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=Sheets("Completed").Range("A" & LR)
        Target.EntireRow.Delete shift:=xlUp
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If
End If
End Sub

 

[ashleyditta]

Thank you! Thank you!! It works perfect!

 

[ebcopystop]

The below code will monitor column E. When a value in column E changes to "Done" it will perform the task you are requesting.

Right click on the sheet tab for "Names" and choose "View Code". Copy/paste the below code into the window that opens up, and that should complete the installation of the code. Be sure to test this on a copy of your data.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR      As Long
If Not Intersect(Target, Range("E:E")) Is Nothing Then
    If Target.Value = "Done" Then
        'Copy row to "Completed" sheet and delete row
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        LR = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=Sheets("Completed").Range("A" & LR)
        Target.EntireRow.Delete shift:=xlUp
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If
End If
End Sub

Hi! This works great and is almost exactly what I need!

BUT, I need to be able to insert rows in the worksheet (the one that is deleting the row). With this code, when I Insert a new row, I get the Type Mismatch debug error on 'If Target.Value = "Done" Then'.
1. I could eliminate this all together if I could figure out how to auto-sort by date (Column A) once a date is added
1a. I have 2 header rows that would not be included in the sort

Bonus question:
Would I also be able to auto-sort by date (Column A) the target sheet once the row is added to it?