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

Mary
You say:
"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."

That is hard to follow. If you wish, send me, direct, a sample of the 2
files. Include only about 10% of your data in each file. Also include an
explanation of what you want, with some examples clearly showing in the
files what you want to happen. Pretend that you have to explain to a new
employee who just walked into your office this morning and knows absolutely
nothing about anything you are doing. Between you and me, we'll get you a
macro that will do what you want.
My email address is . Remove the "nop" from this
address. HTH Otto


"Mary" wrote in message
...
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