Code to transfer data from one workbook to another

[Fergus]

I have one workbook called "Records.xlsx" in column A from A2 downwards are a list of reference codes, look like G1.01.010 etc. In row 1 starting from C1 are a series of numbers. In each column C, D, E etc from row 2 downwards are quantities, ie. numbers.

I have a second workbook called "Invoice.xlsx" in column A from A2 downwards there are reference codes. The codes will be some, but not all of the codes in "Records.xlsx" and not necessarily in the same order. I want to be able to enter a number into cell C1 of "Invoice.xlsx" which will then lookup each code in the first column, then go to "Records.xlsx" find the column which corresponds to the number in C1 and then the quantity in the row that corresponds to the code in column A and enter that number into "Invoice.xlsx".

I want to be able to activate this from a button on "Invoice.xlsx"

Can anyone suggest a code that could do this?

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/sho...to-transfer-data-from-one-workbook-to-another

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fergus]

Sorry about that. It's more than 8 years since I last posted here and the rules have changed.

 

[Fluff]

How about

Sub UpdateInvoice()
   Dim Invws As Worksheet
   Dim Recws As Worksheet
   Dim Cl As Range
   Dim Col As Variant
   
   Set Invws = ThisWorkbook.Sheets("Detail")
   Set Recws = Workbooks("Records.xlsm").Sheets("Quants")
   
   Col = Application.Match(Invws.Cells(1, 3), Recws.Rows(1), False)
   If IsError(Col) Then Exit Sub
   With CreateObject("scripting.dictionary")
      For Each Cl In Recws.Range("A2", Recws.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, Col - 1).Value
         Debug.Print .Item(Cl.Value)
      Next Cl
      For Each Cl In Invws.Range("A2", Invws.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 2).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[Fergus]

That's brilliant, thank you very much. I am still trying to work out how it does that. I don't understand how the CreateObject("scripting.dictionary") works, especially as in my real project I have 3500 rows in both the Records file and the Invoice file. It must somehow create a huge store before copying back into the Invoice but I cannot see how it does that!

I managed to add to the code to make it run whenever the cell containing the number on the invoice is changed.

So many thanks

 

[Fluff]

Glad to help & thanks for the feedback.

For more info on Dictionaries have a look here https://excelmacromastery.com/vba-dictionary/