Hi - I've been trying to write a macro that will cut and paste several rows from a workbook into another file based on a defined range of criterion. At the moment, I've managed to come up with the below, but I have several questions - hopefully some of the good folk here can assist..

Sub TESTRUN()

Dim Criteria As Range
Dim count As Integer
ThisWorkbook.Sheets("range").Activate
count = 2
Range("A2").Select
Criteria = Range(Selection, Selection.End(xlDown))
ActiveSheet.Paste
Workbooks.Open Filename:="q:\test.xls"
ActiveWorkbook.Sheets("STUFF").Activate
For i = 1 To 65536 Step 1
If Cells(i, 5).Value = "TARGET" Then
Cells(i, 1).EntireRow.Cut
Windows("results.xls").Activate
ActiveWorkbook.Sheets("Result").Activate
count = count + 1
Rows(count).Select
ActiveSheet.Paste
Windows("test.xls").Activate
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub

a) When I run the above code, for some reason it skips adjacent rows next to each other even if they match my criterion. For instance, if in my test.xls I have data in rows 10 and 11 containing the value "TARGET" in column E, the macro seems to skip row 11. It seems to only occur when there are adjacent rows - I have no idea why.

b) could someone suggest a more efficient way of writing this instead of simply shuffling between windows? or any other general improvements? (is there a way to cut, paste, and delete a target row without having to shuffle back and forth the way I've done up there?

c) as you can see I've defined a range "Criteria" above - but I don't know how to construct a loop that will search each row in test.xls for any row which matches any of the values in "Criteria" (it's just one column of text values manually updated by the user - and these text values only need to be searched for in one column along any given row in test.xls.

Would greatly appreciate help as my sanity is on the line here... thanks!