synetic,
Hmmm...the code ran fine. I thought about it for a while and cleaned it up.
Try this and let me know:
<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> FindCopySort()<br> <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br> <SPAN style="color:#007F00">'This loops through a column looking for "WIP"</SPAN><br> <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> Sheets("Sheet1").Range("A65536").End(xlUp).Row <SPAN style="color:#007F00">' this starts the loop at row 1 and stops at the last row found</SPAN><br> <SPAN style="color:#00007F">If</SPAN> Sheets("Sheet1").Range("A" & x).Value = "WIP" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Looking for "WIP" in column A</SPAN><br> <SPAN style="color:#007F00">'The three below are just examples of one way to copy, by setting a cell on one sheet equal to a cell on another</SPAN><br> lastrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1<br> Sheets("Sheet2").Range("A" & lastrow).Value = Sheets("Sheet1").Range("A" & x).Value<br> Sheets("Sheet2").Range("B" & lastrow).Value = Sheets("Sheet1").Range("B" & x).Value<br> Sheets("Sheet2").Range("C" & lastrow).Value = Sheets("Sheet1").Range("C" & x).Value<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> x<br> <SPAN style="color:#007F00">'This sorts your list alphabetically using column B as the key sort column</SPAN><br> Sheets("Sheet2").Select <SPAN style="color:#007F00">' this selects the sheet to look at</SPAN><br> Columns("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _<br> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br> DataOption1:=xlSortNormal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
Gary