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.