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?

[mana]

Invoice.xlsm ?

Option Explicit


Sub test()
    Dim ws As Worksheet
    Dim m
    Dim tbl As String
    
    Set ws = Workbooks("Records.xlsx").Worksheets(1)
    
    m = Application.Match(Cells(3), ws.Rows(1), False)
    If IsError(m) Then Exit Sub
    
    tbl = ws.Cells(1).CurrentRegion.Resize(, m).Address(, , , True)
    
    With Cells(1).CurrentRegion.Columns(3)
        .Formula = "=iferror(vlookup(a1," & tbl & "," & m & ",False),"""")"
        .Value = .Value
    End With
    
End Sub

[Fergus]

Thank you for your reply mana. "Invoice xlsm" yes. It is so many years since I last used vba that I have forgotten most of it and I am struggling to convert your code to fit the actual circumstance I have. My original post included a simplified example of what I was trying to do. If I can't make it work maybe I'll have to come back to you with a more detailed example of my problem.

[Fergus]

How can I post a sample of my worksheet to explain my objective