try:

Sub tst()

Dim t, r, nbr, mySets(100, 100) As Variant

nbr = 0
Sheets("Sheet1").Select
r = Range("A65500").End(xlUp).Row
For t = 1 To r
If Cells(t, 3) = "YES" Then
nbr = nbr + 1
mySets(nbr, 1) = Cells(t, 1)
mySets(nbr, 2) = Cells(t, 2)
mySets(nbr, 3) = Cells(t, 3)
mySets(nbr, 4) = Cells(t, 4)
End If
Next

Sheets("Sheet2").Select
r = Range("A65500").End(xlUp).Row
For t = 1 To r
If Cells(t, 3) = "YES" Then
nbr = nbr + 1
mySets(nbr, 1) = Cells(t, 1)
mySets(nbr, 2) = Cells(t, 2)
mySets(nbr, 3) = Cells(t, 3)
mySets(nbr, 4) = Cells(t, 4)
End If
Next

Sheets("Sheet3").Select
For t = 1 To nbr
Cells(t, 1) = mySets(t, 1)
Cells(t, 2) = mySets(t, 2)
Cells(t, 3) = mySets(t, 3)
Cells(t, 4) = mySets(t, 4)
Next

r = Range("A65500").End(xlUp).Row
Range("A1:D" & r).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select

End Sub