vlooup dynamic location

[whiteknight1]

I have several Excel files that are identification cards for suppliers (an Excel file for each supplier) and I want to retrieve the data for all suppliers in one Excel file

For example

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2$,'"["& Range("a2").Value & ".xlsm]sheet1"'!$BC$50:$BD$343,2,FALSE)"


I want the file name to be changed automatically based on the name of the supplier, based on the “t6” field.

[jdc2000]

What, exactly, is in the T6 cell? Is it the supplier name? Do any of your supplier names contain characters that would be illegal for a file name?

Something like this:

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2$,'"["& Range("a2").Value & Range("t6").Value & ".xlsm]sheet1"'!$BC$50:$BD$343,2,FALSE)"