The following code may do the task for you:-
Application.ScreenUpdating = False
Dim lr As Long
Dim i As Integer
lr = Range("C" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If UCase(Cells(i, 3).Value) = UCase("Yes") Then
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
The code searches the Agent column (Column C) in sheet1 for any "Yes" values and transfers the relevant row of data to sheet2. It then deletes the "used" data from sheet1.
Following is the link to my test work book for you to play with. Click on the "RUN" button to see it work. In the sample, four rows of data should be transferred to sheet2.
Let us know if it works for you.
I hope that this helps.