Mary
Loop through the cells in column A of 1.xls and use the Find method to see
if there's a match in column B of 2.xls. If there is, copy it, move it,
whatever you want. It might look like this
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("1.xls").Sheets(1)
Set wsFind = Workbooks("2.xls").Sheets(1)
Set wsDest = Workbooks("2.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.Copy wsDest.Range("A65536").End(xlUp).Offset(1,
0)
End If
Next rCell
End Sub
--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com
Mary wrote:
Hi I have two spreadsheets
Any of the account numbers in column A, spreadsheet name
1.xls found in spreadsheet 2.xls in column B should have
their entire row removed and added to a new sheet in 2.xls
I cant work out how to cycle through the rows in coulumn
A in 1.xls each at a time checking through all the rows
in 2.xls coulumn B spitting them out into a new sheet if
they match (This would include any part numbers picked up
in the search), Then moving on to the next in 1.xls
column A.
Can anyone help me on this?
Thanks
Mary