Merge 2 unrelated workbooks
try this.
run this macro
If you need help on how to do this let me know
sub macro1()
Sheet1.Select
Dim i As Integer
Dim cost2007 As Variant
For i = 1 To 4000 ' Number of rows on Sheet A
cost2007 = ""
With Sheet2.Range("A1:A1500") ' Range on you sheet B
'MsgBox Sheet1.Cells(i, 1).Value
Set c = .Find(Sheet1.Cells(i, 1).Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
cost2007 = Sheet2.Cells(c.Row, 4).Value
' MsgBox c.Address & " " & Sheet2.Cells(c.Row, 4).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Sheet1.Cells(i, 4).Value = cost2007
Next i
End Sub
Jerid B wrote:
Hard to explain, but here it goes:
Spreadsheet A has 4 columns:
Part #, Description, Current Cost, 2007 Cost
In the part number column there is 4000 parts. We only make 1500 of those
(it's a Customers Spreadsheet). All the fields are filled in except 2007
Cost. They want our parts 2007 price put in there.
Spreadsheet B has our data... 4 columns
Part #, Description, Current Cost, 2007 Cost.
1500 parts. Everything filled in, including 2007 Cost.
I want Spreadsheet A to say, if Spreadsheet B has a part number that matches
A, pull the 2007 cost from spreadhseet B and put it in spreadsheet A 2007
cost field.
So in theory, A would update the parts price from spreadsheet B.
Hope this isn't confusing... example:
SpreadSheet A
Part Desc 06 Price 07 Price
1 block 5.00
2 sphere 7.00
Spreadsheet B
Part Desc 06 Price 07 Price
1 blk blk 4.50 6.50
2 rd crcl 6.50 9.50
We have different desc, different 06 price (it's our sell price compared to
theirs), however the part number is the same. However they might have part
number 3 and we don't. The one's they have that we have I want the 2007 price
to fill in their sheet.
Thank you in advance!
|