0

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
Rob Blagg
  • 260
  • 4
  • 13
  • 3
    Possible duplicate of [VBA copy rows that meet criteria to another sheet](http://stackoverflow.com/questions/21074874/vba-copy-rows-that-meet-criteria-to-another-sheet) – B001ᛦ Feb 16 '17 at 11:53

1 Answers1

1

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
Tim Wilkinson
  • 3,761
  • 11
  • 34
  • 62
  • Hi Tim, that is useful. However how can I only copy cells A2-E2 for example if the value was Rob at E2 to a specific point in my 2nd worksheet. – Rob Blagg Feb 16 '17 at 12:34
  • 1
    There seems to be typographical error in your final edit. I feel that the line ~For Each cell In Sheets("EMAIL DATA").Range("E" & Cells(Rows.Count, 5).End(xlUp).Row)~ should read as ~For Each cell In Sheets("EMAIL DATA").Range("E1:E" & Cells(Rows.Count, 5).End(xlUp).Row)~ – skkakkar Feb 16 '17 at 15:38
  • Thanks for your help Tim. – Rob Blagg Feb 17 '17 at 08:42