Generating a new workbook from two separate but similar ones

[comp.databases.pick]

I need to do something relatively simple, and quickly--

I have two workbooks open. Both with similar sets of values - part
numbers and prices, but in different columns.

What I want to do is have Excel take those columns in each workbook,
and the associated price next to them, and paste them into a new
workbook or worksheet with the difference in price beside those
columns. They are not in the same listed order in each original
worksheet.

Example:

Workbook 1: Workbook 2:
PART1 $50 PART365: $50.99
PART3 $30 PART053: $123.30

New workbook (generated by Excel?)

PARTSfromW1 Price PartsfromW2 Price DIFFERENCE
PART1 $45.95 PART1 $65.02 -$19.07

Can anyone help me with this?

Thanks,
Gabe

 

[Incidental]

Hi Gabe

The code below would be one way to do it though it is ruff round the
edges you should be able to modify it to suit your needs. Create a
new workbook add a module and then add the following code. This code
should work as long all the parts listed in the second sheet are also
listed in the first sheet.

Option Explicit
Dim MyCell, Myrng As Range
Dim FCell As Range
Dim LstRow As Integer
Sub Compile_WkShs()

Workbooks.Open "C:\Test\Book1.xls"
Workbooks.Open "C:\Test\Book2.xls"

Workbooks("Book1.xls").Activate
Sheets(1).Activate
LstRow = [A65535].End(xlUp).Row
Range("A1:B" & LstRow).Copy

Workbooks("Book3.xls").Activate
Sheets(1).Activate
[A1].PasteSpecial
Application.CutCopyMode = False

Workbooks("Book2.xls").Activate
Sheets(1).Activate
LstRow = [A65535].End(xlUp).Row
Range("A1:B" & LstRow).Copy

Workbooks("Book3.xls").Activate
Sheets(2).Activate
[A1].PasteSpecial
Application.CutCopyMode = False

Set Myrng = Range("A1:A" & LstRow)

Sheets(1).Activate
Columns("A:A").Select

For Each MyCell In Myrng

Set FCell = Selection.Find(What:=MyCell, LookAt:=xlWhole)

FCell.Offset(0, 2).Value = MyCell
FCell.Offset(0, 3).Value = MyCell.Offset(0, 1)
FCell.Offset(0, 4).Value = FCell.Offset(0, 1) - FCell.Offset(0, 3)

Next MyCell

End Sub


hope this is of some use to you

S