VB Code to move data from one sheet to another

[Matt]

Please can someone help me with the following scenario. I want to look through column B in Sheet 1 and where the word "complete" is present in column B, I want to remove the entire row and paste it into sheet 2, starting at the first blank row. Any suggestions for the required code would be greatly appreciated.

Thanks

Matt

[ This Message was edited by: Matt on 2002-04-16 11:23 ]

[ This Message was edited by: Matt on 2002-04-16 11:38 ]

[Jay Petrulis]

Hi Matt,

This code worked for me. Let us know if it works for you.

---begin VBA---
Sub tester()
Dim rng As Range, usedcell As Range
Dim lastrow As Long, x As Boolean

Set rng = Intersect(Sheets("Sheet1").UsedRange, _
Sheets("Sheet1").Range("B:B"))

For Each usedcell In rng
x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")
If x Then
lastrow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet1").Rows(usedcell.Row).Copy Sheets("Sheet2").Rows(lastrow + 1)
End If
Next usedcell

End Sub
---end VBA---

HTH,
Jay

EDIT: If you want to cut the data rather than copy it to the new sheet, change

Sheets("Sheet1").Rows(usedcell.Row).Copy Sheets("Sheet2").Rows(lastrow + 1)

to

Sheets("Sheet1").Rows(usedcell.Row).Cut Sheets("Sheet2").Rows(lastrow + 1)


[ This Message was edited by: Jay Petrulis on 2002-04-16 12:13 ]

[Matt]

That's great!Thanks for your help Jay