I wrote this in responce to R.VENKATARAMAN they wrote a
reply and then forgot about the post.

Here is my problem

-

I have two workbooks. One contains around 5500 rows of
records (1.xls) and another (2.xls) containing 400 rows.

I need to separate out from 1.xls all of the records it
has which match those in 2.xls. Sometimes there will be
up to 6 rows in 1.xls matching that in 2.xls. The column
which I need to match the records to is column H in
1.xls, the column to get the search criteria from is in
column B in 2.xls.

Any matching rows found in 1.xls I would like to add to a
new sheet in 1.xls deleting the original row from the
main sheet, carrying on to the end extracting any others
matching before moving on to the next in 2.xls to search
for that one.

I have been using the following code to loop through
2.xls, sheet 1, column B taking the value, Checking in
1.xls through all records for a match in column H. I am
not sure if the code will only work if it is in a module
of one or the other workbooks; but I cant get it to work.
It just seems to cut and paste the row from 2.xls into a
new sheet in 1.xls when it finds the first match in
1.xls.



code:
----------------------------------------------------------
----------------------

Sub MoveMatches()

Dim wsSrc As Worksheet
Dim wsFind As Worksheet
Dim wsDest As Worksheet
Dim rCell As Range
Dim rFound As Range

Set wsSrc = Workbooks("2.xls").Sheets(1)
Set wsFind = Workbooks("1.xls").Sheets(1)
Set wsDest = Workbooks("1.xls").Sheets(2)

For Each rCell In wsSrc.Columns(1).Cells
Set rFound = wsFind.Columns(2).Find(rCell.Value)

If Not rFound Is Nothing Then
rCell.EntireRow.Cut Destination:=wsDest.Range
("A65536").End(xlUp).Offset(1, 0)
End If

Next rCell

End Sub

----------------------------------------------------------
----------------------


Thanks

Mary