Excel: Copy and paste to different sheet, secified column range from each row that meets a condition

Hi All, I'm sure this has been done before, and likely out there a hundred times already, but for the life of me I can't find it. I'm looking to copy columns H through to AO, from each row on the worksheet "Data" that has "Yes" in column A and then paste those ranges into the sheet called "Extract", building a list down from G5 (G4 has the heading). I've got this thus far:

Sub CopyYesRows()
For Each cell In Sheets("Data").Range("a:a")
    If cell.Value = "Yes" Then
        Range(Cells(ActiveCell.Row, 8), Cells(ActiveCell.Row, 41)).Copy   '8=ColH  41=ColAO
            If Sheets("Extract").Range("g5") = "" Then
                Else: Sheets("Extract").Range("G4").End(xlDown).Offset(1, 0).PasteSpecial
            End If
    End If
End Sub
But its not pulling through the data, just grabs the range from the row that was active before triggering the code - I believe due to reference to active row, where I'm not actually selecting/activating each row that has Yes in A:A. How can I ditch the Activecell.row reference to be 'row on which you've found the Yes in A:A" as I'm keen to not have lots of selection going on. OR how else can I achieve the result please? Many thanks in advance. Upex

This question generated 10 answers. To proceed to the answers, click here.

This thread is current as of July 29, 2017.

For more resources for Microsoft Excel