VBA Coding to paste rows in other sheet

[Rajesh Bhapkar]

Hi, I am trying to use macro to shift cells from one sheet to another once the status of the tasks is changed to completed.

I want the program to do the following
Look in column U to find the status completed.
Then Select the complete row, Copy it and paste into another sheet which is completed tasks 2012 in the blank row after the last filled row
And then delete the cell from the first sheet (that is task list)

I tried but i am not able to work out how to look for the next blank row in sheet 2 for pasting and how to loop the program till all rows with completed status are shifted to the next sheet.

Kindly help

Sub Auto_Open()
'
' Auto_Open Macro
'

'
Do While True
Cells(1, U).Find(What:="Completed", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cell.EntireRow.Select
Selection.Copy
Sheets("Completed Tasks 2012").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Sheets("Task List").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-342
Columns("U:U").Select
Selection.FindNext(After:=ActiveCell).Activate
If Nothing Then Exit Do
Loop

End Sub

[Rajesh Bhapkar]

The code i used is this
Sub Auto_Open()
'
' Auto_Open Macro
'

'
Columns("U:U").Select
Do While True
Cells(1, U).Find(What:="Completed", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cell.EntireRow.Select
Selection.Copy
Sheets("Completed Tasks 2012").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Sheets("Task List").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
If Nothing Then Exit Do
Loop

End Sub