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

[Upex]

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
        [B]Range(Cells(ActiveCell.Row, 8), Cells(ActiveCell.Row, 41)).Copy[/B]   '8=ColH  41=ColAO
        
            If Sheets("Extract").Range("g5") = "" Then
               Sheets("Extract").Range("g5").PasteSpecial
            
                Else: Sheets("Extract").Range("G4").End(xlDown).Offset(1, 0).PasteSpecial
            End If
    End If
Next
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

 

[Upex]

Think I understand, kind of.

Many thanks for your help with Mark, much appreciated, as without you, I'd not have got this working.

Owe you a few beers!

Cheers, Upex