Move data to new sheet

[asdaftasabrush]

I am wanting to move select data to another existing sheet in my spreadsheet. Any that say "Yes" will need the first column data and the reason (last column) to be brought across to the other sheet. In this case the "Sad" and "Angry" data is not needed on the new sheet for the report.

Example is not data I will collect:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]Happy[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]He is having a good day[/TD]
[/TR]
[TR]
[TD="align: center"]Sad[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Maybe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Angry[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Ecstatic[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Got a new job[/TD]
[/TR]
[TR]
[TD="align: center"]Bored[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]hates his job[/TD]
[/TR]
</tbody>[/TABLE]

If this could be a macro that would be perfect. Thanks!!

 

[dfsobral]

select column with Yes information (not all column just the cells in the table

then try this

Sub move()
Dim rng As Range
Dim cell As Range
Dim iCount As Long
Dim myArray() as Variant


Set rng = Selection
ReDim myArray(1 To Application.WorksheetFunction.CountIf(rng, "Yes"), 1 To 2)


For Each cell In rng
    If cell.Value = "Yes" Then
        iCount = iCount + 1
        myArray(iCount, 1) = cell.Offset(0, -1).Value
        myArray(iCount, 2) = cell.Offset(0, 3).Value
    End If
Next cell


Sheets("Name of existing destination worksheet").Activate


'assuming that new destination begins in cell A1, if not adjust destination
Range(Cells(1, 1), Cells(UBound(myArray, 1), 2)) = myArray


End Sub