Find a value, copy the row, move to next row.

It jumps back and forth between sheets but is only overwritting the data into a2:k2

Originally Posted by AB33

Sub copyrows()
     
    Dim tfCol As Range, Cell As Object
     
    Set tfCol = Range("j2:j" & Cells(Rows.Count, "J").End(xlUp).Row)
     
    For Each Cell In tfCol
         
        If IsEmpty(Cell) Then
            Exit Sub
        End If
         
        If Cell.Value = "Startup" Then
            Cell.Offset(, -9).Resize(, 11).Copy
            Sheets("Startup").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
           
        End If
         
    Next
     
End Sub

This is better code

Sub test()
Dim i&, LR&
With ActiveSheet
  LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
  For i = 2 To LR
   If Len(.Cells(i, 10)) Then
        If Trim(.Cells(i, 10)) = "Startup" Then
        .Cells(i, 1).Resize(, 11).Copy
         Sheets("Startup").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
        End If
     End If
  Next i
 End With
End Sub