The code expects the two workbooks to be opened. Will create a newworkbook,
but doesn't save the new book. I copied the entire row when the part number
did not match. If you need additional changes let me know.




Sub comparebooks()

Set CCCPNum_bk = Workbooks("CCC Part Numbers.xls")
Set CCCPNum_sht = CCCPNum_bk.Sheets("CCC Part Numbers")

Set CurPNum_bk = Workbooks("Current Part Numbers.xls")
Set CurPNum_sht = CurPNum_bk.Sheets("Current Part Numbers")

Set newbk = Workbooks.Add
Set newbk_sht = newbk.Sheets("Sheet1")
NewbkRowCount = 1

With CCCPNum_sht
CCCRowCount = 1
Do While .Range("A" & CCCRowCount) < ""
CCCPNum = .Range("A" & CCCRowCount)
With CurPNum_sht
Set c = .Columns("A:A").Find(what:=CCCPNum, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
c.EntireRow.Copy _
Destination:=newbk_sht.Rows(NewbkRowCount)
NewbkRowCount = NewbkRowCount + 1
End If
End With
CCCRowCount = CCCRowCount + 1
Loop
End With

End Sub

"Chris Hankin" wrote:

Hello, could someone please help with the following?

I wish to undertake a two-way data comparison as follows:

Workbook1 Name: CCC Part Numbers.xls
Worksheet1 Name: CCC Part Numbers

and

Workbook2 Name: Current Part Numbers.xls
Worksheet1 Name: Current Part Numbers


Column A ( in Workbook: CCC Part Numbers.xls) contains a list of 5500
part numbers - for example:

Cell A2: 001-PL-77RFC
Cell A3: A-56KL-001
Cell A4: 0029-YYTF-5578
Cell A5: GGF002-96PMC-05
Cell A6: 6003-JHQ
Cell A7: Z-PUNR-74100

Column A (in Workbook: Current Part Numbers.xls) also contains a list of
500 part numbers - for example:

Cell A2: A85-KLM
Cell A3: A-56KL-001
Cell A4: 0029-YYTF-5578
Cell A5: H005-TYB-417
Cell A6: 6003-JHQ
Cell A7: Y001-9874185-964

Both workbooks are identical in layout and structure as follows:

Column C contains the associated Item Names for the part numbers listed
in column A.

Column D contains the associated Supplier Codes for the part numbers
listed in column A.

Column E contains the associated Codified Product Numbers for the part
numbers listed in column A.

Column F contains the associated Unit of Measures for the part numbers
listed in column A.

Column G contains the associated Costs for the part numbers listed in
column A.

Column H contains the associated Storage Locations for the part numbers
listed in column A.

Column I contains the associated Product Types for the part numbers
listed in column A.

I need to compare the data in column A of (Workbook: CCC Part
Numbers.xls) against the data in column A of (Workbook: Current Part
Numbers.xls) and all those values in column A of (Workbook: CCC Part
Numbers.xls) that do not match a value in column A of (Workbook:
Current Part Numbers.xls) need to be copied to a new (Workbook named:
Matched Data) and the associated values in columns C to columns I need
to be copied to this new workbook.

Any help would be greatly appreciated,

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***