CODEWITHSUNDEEP
vbaexcel
Rob Blagg
Rob Blagg

Reputation: 280

How do I loop through a column, if a cell matches some criteria then paste that whole row into another worksheet

I have the below code which selects the value I want however I want to know how I can then copy that row based on finding that value then paste into another worksheet at a specific point e.g. Worksheet 2 E5. 

For Each cell In Sheets("EMAIL DATA").Range("E:E")  
    If cell.Value = "Rob" Then
        'Do something
    End If
Next

Upvotes: 0

Views: 569

Answers (1)

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

You can't paste a row into cell E5 for example, because the copied row will be longer than the space you allow to paste into

For Each cell In Sheets("EMAIL DATA").Range("E:E")  
    If cell.Value = "Rob" Then
        Worksheets(2).Range("E5").EntireRow.Value = cell.EntireRow.Value
    End If
Next

But that will keep over riding the same row, better something like

i = 1
For Each cell In Sheets("EMAIL DATA").Range("E:E")  
    If cell.Value = "Rob" Then
        Worksheets(2).Cells(i, 1).EntireRow.Value = cell.EntireRow.Value
        i = i + 1
    End If
Next

Also look into getting the last cell in column E so you're not wasting time iterating all 1 millions rows, like Sheets(1).Range("E" & Cells(Rows.Count, 5).End(xlUp).Row)

EDIT

As per your comment

i = 1
For Each cell In Sheets("EMAIL DATA").Range("E" & Cells(Rows.Count, 5).End(xlUp).Row)
    If cell.Value = "Rob" Then
        Worksheets(2).Cells(i, 1).Resize(, 5).Value = Cells(cell.Row, 1).Resize(, 5).Value
        i = i + 1
    End If
Next

Upvotes: 1

Related Questions