Move entire row to new worksheet based on specific value

Hi all...I am new to the forum and I have a problem that is almost identical to a post that is in this forum from Sept of 2011...but, while the concept is almost identical, I cannot manipulate the code to work for me. I have a product backlog worksheet. Each row contains a product problem. When the value in the Status column (Column D) is equal to 'Done', the user would like to click a button and move all rows with that status to an 'Archive' sheet and then be deleted from the current product backlog sheet. When the rows are added to the archive sheet, the user would like them to be automatically placed on the next open row.

The user is sorting the Status column so all rows with a status = 'Done' are all together.

Another problem is I can't seem to save the macro in the worksheet (I use Excel 2010).

I've included a sample spreadsheet....the code below is what I tried to modify but to no avail....

Thanks for any help that you can give....

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
Dim i As Long
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("D:D")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
If LCase(Target.Value) = "completed" Then
i = Target.Row
Target.EntireRow.Cut Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Cells(i, "D").EntireRow.Delete
End If
End Sub

Thanks very much for input and I apologize for mistake with code...appreciate quick reply. I will try this today...

Thanks AB33...the code works perfectly! I now realize the code I included uses the Worksheet_Change Event thinking the user would like it that the macro would execute every time a cell in Column D in its sheet is changed.

But, what if the user would rather not have the row moved and deleted everytime the cell in Column D is changed to 'Done' but rather wait and click a button when the value is equal to 'Done'? So,upon clicking that button, all rows with Status = 'Done' would be moved to an 'Archive' sheet and then deleted from the current product backlog sheet. And like with the code you provided, when the rows are added to the archive sheet, the user would like them to be automatically placed on the next open row?

Thanks again for your help....

Try the attached.
This is not an event-driven code. It will not run auto, but you need to run it.

Thank you AB33!! This seems to do the trick...I attached your macro to a command button and it works like a charm....Being a novice at VBA, I never would have been able to achieve this...thanks again!

Hello,

I am trying to do the same thing as D0lph1n - I copied the code however I am getting a runtime error for the line
With Sheets ("name of sheet")

Any ideas what that might be?