I am using the below code to look up data in another workbook and collect the data. I have been able to get it to work in book 2.xlsm (code is in this "copydata") for my data entry sheet that is located in test.xlsx.
What I want to be able to do is also get VBA to pull the integral data from the sample 1, sample 2, etc. in test.xlsx and place in book2 integral values. These sheet names/sample names in test.xlsx will change based on the sample name(will be dynamic), but will be the same names in both workbooks.
Can someone help guide me to how I can add on to this code and address this? I am new to VBA so I am still learning. My actual documents are much larger and so I will need to tweak the reference cells in the end so please try to explain what some of the things mean so I know what I will be doing.
Additionally, is there a way I can dynamically reference the external workbook name in cell A1, instead of the way I have it defined currently so I don't have to change the name every time?
Thanks!
Sub copydata()
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("/Users/username/desktop/test.xlsx")
Set x = extwbk.Worksheets("Data entry").Range("A1:GZ400")
With twb.Sheets("Sheet1")
For rw = 4 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 11, False)
Next rw
End With
With twb.Sheets("sheet1")
For rw = 4 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 3) = Application.VLookup(.Cells(rw, 1).Value2, x, 12, False)
Next rw
End With
extwbk.Close savechanges:=False
End Sub
Display More