Cut a row from "sheet 1" and paste onto "sheet2", run-time error '1004' "Paste method of Worksheet class failed"

[csouth]

Good morning. I am new to vba and am having a little trouble understanding why I am receiving an error message. I have built a spreadsheet to track maintenance project in a facility. This spreadsheet will automatically change the value of the cells in column 'J' form "In Progress" to "Completed" when a value is inputted into the column 'G'. I had hoped to be able to write a macro that would check the value of column 'J', one row at a time, and if the value were "Completed" then it would cut that row, open sheet2, find the next empty row, paste the cut row, and delete the row it cut from sheet1. This macro will work perfectly one time. On the second time through, I will get "Run-time error 1004: Paste method of Worksheet class failed". Below is the code and a snip of the worksheet for reference. Any help would be greatly appreciated.

Sub MoveData()
Dim r1, r2
' Determine that last row in the Data Sheet (r1)
Sheets("MAINTENANCE TRACKER").Select
r1 = Range("J65536").End(xlUp).Row

Dim count As Long
For count = 1 To r1
If Range("J" & count).Value = "COMPLETED" Then
DoEvents
Sheets("MAINTENANCE TRACKER").Activate
Rows(count).Cut
Sheets("ARCHIVE COMPLETED").Activate
Sheets("ARCHIVE COMPLETED").Unprotect Password = "1234"
' Determine that last row in the ARCHIVE COMPLETED (r2)
r2 = Range("A65536").End(xlUp).Row
Rows(r2 + 1).Select
ActiveSheet.Paste
DoEvents
Sheets("ARCHIVE COMPLETED").Protect Password = "1234"
Sheets("MAINTENANCE TRACKER").Activate
Rows(count).EntireRow.Delete
count = 1
Application.CutCopyMode = False
End If
Next
End Sub

 

[rpaulson]

think of it as replacing the key word with a delete row command.

you need the temporary "on error resume next" otherwise it will trow an error if the key word is not found.