Forum Discussion
Solved
How to Index Match from multiple closed workbooks in excel.
Dear Tech Community, I am trying to reference multiple closed Excel workbooks with different file paths and file names. My initial approach was to define the file path and file name in different ...
Here is a new sample workbook.
The current values in G5:UU5 are just dummy values.
First, enter some valid lookup values in G5, H5, etc. You don't have to do all of them, just a few for now.
Next, correct the values in A7, B7 and C7, or if they are already correct, select one of them, press F2, then press Enter.
Then look at G7, H7, etc. Hopefully, they will now contain formulas.
If that works, add data for a different workbook in A8 to C8 and then look at G8, H8 etc.
See if this works for you:
Sub Get_Data()
Dim r As Long ' row number
Dim m As Long ' last row
Dim p As String ' path
Dim b As String ' filename
Dim s As String ' cell reference
Dim f As String ' Formula
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
p = Range("B6").Value
m = Range("A" & Rows.Count).End(xlUp).Row
Range("G7:G" & m).ClearContents
For r = 7 To m
If Range("A" & r).Value <> "" Then
If Range("C" & r).Value <> "" Then
b = Range("C" & r).Value
Else
s = p & "\" & b
If Dir(s) <> "" Then
s = "'" & p & "\[" & b & "]" & Range("D" & r).Value & "'!C5"
f = "=IFERROR(" & s & ","""")"
On Error Resume Next
Range("G" & r).Formula = f
On Error GoTo ErrHandler
End If
End If
End If
Next r
ExitHandler:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Thanks a lot for your patience and help on this. This code is working very well and I'm also able to execute it as a macro. Really grateful to you for all of your help on this code 🙂