The following code should do the task for you:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If Target.Value = "Yes" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "E")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
Application.CutCopyMode = False
Application.ScreenUpdating = True
The code finds any cell in Column F that has a "Yes" value from the drop downs and moves the row of data (A:E) to the next available row at the bottom of the existing data set.
The code is a Worksheet_Change event and needs to be placed in the worksheet module. So, right click on the sheet tab, select "view code" from the menu that appears and then, in the big white field that appears, paste the above code. Go back to the worksheet and test it (remember to test the code in a copy of your work book first). Now, every time that you select "Yes", the relevant row of data will be moved to the bottom of the existing data set.
In the code above, I have just referenced sheet 1 as your work sheet. You may have to change this to suit yourself.
Following is the link to my test work book for you to peruse:-
Select "Yes" from the drop downs in any cell in the Criteria column to see the code at work.
I hope that this helps.