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:
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
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:
Code:
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
Last edited: