vlookup to workbook A from workbook B

Question

I want to do a vlookup in workbook A that will pull identical information from workbook B. Both workbooks are already open.

The program should be in a third workbook C.

The name of both workbooks are not the same so I give it a variable format (hence, the program should work for any workbook we set) and the range to the last record from either workbook A or B can vary.

I have an example I recorded but it doesn't work the way I want it.

In my example, I am pulling to workbook B from workbook A. I also trimmed the lookup value from workbook B because of spaces and I want it to match lookup value in workbook A (most doesn't have space, not sure about all). Is there a way trim lookup value in workbook A as well?

Windows("test A.xlsx").Activate
Windows("test B.xlsx").Activate
ActiveCell.FormulaR1C1 = "=VLOOKUP(trim(RC[-1]),'[test A.xlsx]Sheet1'!C1:C2,2,FALSE)"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B30000")
Range("B1:B30000").Select
Range("B1").Select
Windows("mac.xlsm").Activate

Kelvin Kelvin · Accepted Answer · 2017-01-07T14:58:14

Here is the code. check if it works for you. Autofill would not work properly, so it better you identify the lastrow and use with and end with option.

Sub vlookupexample()

Dim FileA As Workbook
Dim FileB As Workbook
Dim fileALastrow As Long
Dim FileBLastrow As Long


Set FileA = Workbooks.Open("C:\Users\raja\Desktop\fileA.xlsx")
Set FileB = Workbooks.Open("C:\Users\raja\Desktop\fileB.xlsx")

FileA.Activate

fileALastrow = FileA.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

FileB.Activate

FileBLastrow = FileB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

FileA.Activate
FileA.Sheets("Sheet1").Range("B2").Select
With FileA.Sheets("Sheet1").Range("B2:B" & fileALastrow)
        .Formula = "=VLOOKUP(RC[-1],[fileB.xlsx]Sheet1!C1:C2,2,0)"
       .Value = .Value
        End With
Msgbox "Process Completed"
End Sub

vlookup to workbook A from workbook B

1 Answers