find/retrieve price from closed WB and copy to current workbook

active worksheet has item numbers in column A

workbook "pricing" has the same item numbers in A and a price in H
(some of the item numbers are duplicated in "pricing" so I need the search for each item number to stop as soon as it is first found)

also the workbook "pricing" may be already opened by a different user.... so read only?

I need the macro to start in A1 of active worksheet and search column A in "pricing" for the item number if found copy H to H of the active sheet.

item number may not be found

The following code I don't prefer because it copies the final instance of price number.
It also isn't closing "pricing" after macro ran

I've seen vlookup or match but am unable to adapt code myself

HTML Code:

---


Sub pricetheinvoice()

    Dim cl As Range  Dim dic As Object  Dim ws1 As Worksheet, ws2 As Worksheet

    Application.ScreenUpdating = False

    Set ws2 = ThisWorkbook.ActiveSheet

    Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Copy of Pricing.xls"   
    Set wb1 = ActiveWorkbook   
    Set ws1 = ActiveSheet       
    Set dic = CreateObject("scripting.dictionary") 

    For Each cl In ws1.Range("A1", ws1.Range("A" & Rows.Count).End(xlUp))     
          dic(cl.Value) = cl.Offset(, 7).Value 
    Next cl 

    For Each cl In ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))     
          cl.Offset(, 7).Value = dic(cl.Value) 
    Next cl

 End Sub

---

Hi lostinvb!

Code:

---

Sub pricetheinvoice()
    Dim cl As Range
    Dim dic As Object
    Dim ws1 As Worksheet, ws2 As Worksheet
    Application.ScreenUpdating = False
    Set ws2 = ThisWorkbook.ActiveSheet
    Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Copy of Pricing.xls", ReadOnly:=True
    Set wb1 = ActiveWorkbook
    Set ws1 = ActiveSheet
    Set dic = CreateObject("scripting.dictionary")
    For Each cl In ws1.Range("A1", ws1.Range("A" & Rows.Count).End(xlUp))
          If Not dic.exists(cl.Value) Then dic(cl.Value) = cl.Offset(, 7).Value
    Next cl
    wb1.Close False
    For Each cl In ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))
          cl.Offset(, 7).Value = dic(cl.Value)
    Next cl
    Application.ScreenUpdating = True
 End Sub

---

sorry for the long pause in reply. This works great. However, I would like to move the macro to my personal workbook. (I did so but then nothing happens when run).
I think it is ( I know it is) looking at the activesheet in the personal workbook not the activesheet I am running the macro on? The name of the open wb that I run the macro on always changes.

answered my own question .... changed the following and it seemed to work:

Set ws2 = ThisWorkbook.ActiveSheet

to

Set ws2 = ActiveSheet

Yes, it needs to be revised according to the actual situation.:beerchug:


--Okami