The following code may 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, Columns("AE:AE")) Is Nothing Then Exit Sub
If Target.Value = "Yes" Then
Cells(Target.Row, "F").Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Range(Cells(Target.Row, "AG"), Cells(Target.Row, "AH")).Copy Sheet2.Range("B" & Rows.Count).End(3)(2)
Application.CutCopyMode = False
Application.ScreenUpdating = True
It is a Worksheet Change event which means that every time you enter "Yes" in any cell in Column AE, the three pieces of data that you need will be transferred to sheet2 immediately.
Following is the link to my test work book for you to peruse:-
Enter "Yes" in any cell in Column AE and the relevant data will be transferred to sheet2.
In the above code, should you wish to delete the entire row of data once the relevant data has been transferred to sheet2, just remove the apostrophe from the begining of line 13 (in the green font).
I hope that this helps.