copying rows one at a time to another sheet ones a criteria has been forfilled

[solo_razor]

hello,

I want to set up a code that will search in a sheet for a certain value
within a single cell in a fixed column, once this criteria has been
done then this entire row must be copied into another sheet.
Does anybody know a good code for doing such an operation with a
minimum of code.

Regards,
Niek

 

[Ron de Bruin]

Try this with your data in Sheet1
It will copy the row to sheet2 if "ron" is in the Acolumn

Sub copy_to_other_sheet()
Dim a As Long
Dim i As Long
With Sheets("sheet1")
i = .Cells(Rows.Count, "A").End(xlUp).Row
For a = 1 To i
If .Cells(a, "A").Value = "ron" Then .Cells(a, "A").Copy _
Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next a
End With
End Sub

 

[Don Guillett]

Try this. Modify to suit
Sub findandcopy()
Columns("B").Find("x").EntireRow.Copy _
Sheets("sheet2").Range("a1")
End Sub

Here is one to delete the row
Sub findanddelete()
Columns("B").Find("x").EntireRow.Delete
End Sub