Excel VBA lookup across another workbook sheets

Question

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 for my data entry sheet that is located in test.xlsx

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

What I want to be able to do is also get VBA to pull (I'm guessing using a combo of indirect and vlookup?) the integral data from the additional sheets in test.xlsx and place in book2 integral values. These sheet names in test.xlsx will change based on the sample name, but those names will be the same names that are in sheet1 of book2, same for the integral names.

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.

Answer 1

Note: working code at the end.

I'd say that the easiest way to act is to write a working formula for the range you want to fill. You can then start to record a macro of you typing the formula. With some edit, you can properly insert the resulting code in your subroutine.

STEP 1: writing the formula.

Since in your subroutine the test.xlsx will be open, your formula can be written and tested while test.xlsx is opened. You already have rightfully guessed the formula you need (VLOOKUP and INDIRECT). But for the sake of explanation, let's assume you've started with a simplier formula, like this one for the cell D4:

=VLOOKUP(D$3,'[test.xlsx]sample 1'!$D:$H,5,FALSE)

To make it dynamically choose the right sheet, we need to edit the table_array part. Within it, two parts are constant: '[test.xlsx] and '!$D:$H. They can be written as strings. The sample 1 is contained in the first cell of the row, so we will just write a reference to it. Our formula will therefore look like this:

=VLOOKUP(D$3,INDIRECT("'[test.xlsx]" & $A4 & "'!$D:$H"),5,FALSE)

Our formula is fairly functional. Let's record the macro.

STEP 2: recording and editing the macro.

Start the recording, select the cell with the formula, press F2, press enter, stop the recording. You can then go to VBA and there you'll find (presumably in a new module) the macro you've just recorded. It will most likely look like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("D4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(R3C,INDIRECT(""'[test.xlsx]"" & RC1 & ""'!$D:$H""),5,FALSE)"
    Range("D5").Select
End Sub

Of all this code, what we really care about is the .FormulaR1C1 = "=VLOOKUP(R3C,INDIRECT(""'[test.xlsx]"" & RC1 & ""'!$D:$H""),5,FALSE)". As you can see, it changes the property FormulaR1C1 of the given range. Basically we can insert a string that (if correctly formatted) it will be read as a formula with RC (row-column) type references. More information about it here. While integrating this formula, we can also change the '[test.xlsx] part to use a reference depending on our code. Thefore we change this:

.FormulaR1C1 = "=VLOOKUP(R3C,INDIRECT(""'[test.xlsx]"" & RC1 & ""'!$D:$H""),5,FALSE)""

into this:

.FormulaR1C1 = "=VLOOKUP(R3C,INDIRECT(""'[" & extwbk.Name & "]"" & RC1 & ""'!$D:$H""),5,FALSE)"

This way in case extwbk had a different name, the resulting formula would still work. We could also do the same thing basically with all the variables in the formula.

Now we need to determine the formula's range of destination. We already have a range variable in our code that we can use. Mind that a single letter name for a variable is not the best. You should choose a name with at least 3 letters that you presumably won't find in the rest of the code. This will make easier the search and eventually the edit of the given variable. It's also a good practice to add a "tag" to the variable to underline what kind of variable it is (example Rng if it's a range, like RngMyCell). It can be also risky to use the same vaguely called variable for different purpouse in a code, but since it's a really short code we should be fine (and you can still improve the code accordingly later). Anyway, to determine the range we can use Resize and Offset starting from the cell D3 like this:

With twb.Sheets("Sheet1")
    Set x = .Range(.Range("D3"), .Cells(3, .Columns.Count).End(xlToLeft))
    Set x = x.Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 3, x.Columns.Count).Offset(1, 0)
End With

We can then apply our formula to the x range. Since we are interested in the formulas' results and not in the formulas themselves we can add a x.Values = x.Values line to sobstitute the formulas with their results. Our code will therefore be like this:

With twb.Sheets("Sheet1")
    Set x = .Range(.Range("D3"), .Cells(3, .Columns.Count).End(xlToLeft))
    Set x = x.Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 3, x.Columns.Count).Offset(1, 0)
    x.FormulaR1C1 = "=VLOOKUP(R3C,INDIRECT(""'[" & extwbk.Name & "]"" & RC1 & ""'!$D:$H""),5,FALSE)"
    x.Value = x.Value
End With

Our code is ready to be integrated into our subroutine.

STEP 3: integrating the code

We can place our code in our subroutine. But first we can also merge the two With twb.Sheets("Sheet1") since they are identical and put our code within the same with statement. We can also note that in our For-Next cycles a point is missing. The end result of should be like this:

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") '<-- this with is the same as the next one. No need to repeat it.
    
        For rw = 4 To .Cells(.Rows.Count, 1).End(xlUp).Row '<-- the ".Rows.Count" didn't have the point.
            .Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 11, False)
        Next rw
        
        For rw = 4 To .Cells(.Rows.Count, 1).End(xlUp).Row '<-- the ".Rows.Count" didn't have the point.
            .Cells(rw, 3) = Application.VLookup(.Cells(rw, 1).Value2, x, 12, False)
        Next rw
        
        Set x = .Range(.Range("D3"), .Cells(3, .Columns.Count).End(xlToLeft))
        Set x = x.Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 3, x.Columns.Count).Offset(1, 0)
        x.FormulaR1C1 = "=VLOOKUP(R3C,INDIRECT(""'[" & extwbk.Name & "]"" & RC1 & ""'!$D:$H""),5,FALSE)"
        x.Value = x.Value
        
    End With

    extwbk.Close savechanges:=False
End Sub