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
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 Sheets("Extract").Range("g5").PasteSpecial Else: Sheets("Extract").Range("G4").End(xlDown).Offset(1, 0).PasteSpecial End If End If Next End Sub
This question generated 10 answers. To proceed to the answers, click here.
This thread is current as of July 29, 2017.