Cut a row from one sheet and paste it into another based on a specific criteria

[Teuchter]

Help needed please.

I do not have a clue where to start. If in doubt ask an expert.

I have a spreadsheet (2010) which 2 sheets, named: Learners and Completed. I want to delete the content of a row from the "Learners" sheet when column T (starting at T2) contains the word "completed" and paste the entire row into the second worksheet named "Completed" starting at A2 and sequential there after. I would also like the VB to delete the empty row in the first worksheet one this has been done.

If possible I would like this to happen automatically if not by the click of a command button.

Your help would be greatly appreciated.

Thank you in advance

Teuchter

 

[kevatarvind]

try below code

Sub Test()
Dim i As Integer
Dim j As Integer
Sheets("Learners").Select
For i = 2 To Cells(Rows.Count, 20).End(xlUp).Row
    If UCase(Cells(i, 20).Value) = "COMPLETED" Then
        Rows(i).EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next
For j = Cells(Rows.Count, 20).End(xlUp).Row To 2 Step -1
    If UCase(Cells(j, 20).Value) = "COMPLETED" Then
        Rows(j).EntireRow.Delete
    End If
Next
End Sub

 

[Teuchter]

Thank you very much! Just the job.

Teuchter