PDA

View Full Version : Code to transfer data from one workbook to another



Fergus
06-29-2018, 09:56 PM
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
07-01-2018, 04:51 AM
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
07-04-2018, 03:00 AM
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
07-07-2018, 06:51 PM
How can I post a sample of my worksheet to explain my objective