Vlook Up multiple workbook and more

[Pravin_008]

Hi All,

i want to create macro that will open file lets asume book1 (excle file any name) from folder,
go to second sheet B1,
copy value in macro file in A1 cell range,
in macro file b1 i want to do Vlookup formula
e.g: =VLOOKUP(A1,'[book1]Sheet2'!$C:$F,4,0)

i want to create this macro. i try to set variant but dont know how to set in vlookup.

and my main criteria is main file will change with name.

 

[Luke M]

Welcome to the forum!

You don't need to open the file to write the vlookup. Just need to include file path when you write formula. In your XL file, you can write something like:
=VLOOKUP(A2,'C:\My Documents\Cool Projects\[book1.xls]Sheet2'!$C:$F,4,0)
and then you don't need a macro.

 

[Ramesh Deo]

Welcome to the forum!

You don't need to open the file to write the vlookup. Just need to include file path when you write formula. In your XL file, you can write something like:
=VLOOKUP(A2,'C:\My Documents\Cool Projects\[book1.xls]Sheet2'!$C:$F,4,0)
and then you don't need a macro.

Awesome!

 

[Pravin_008]

i have lots of data so i need to create macro. every week my file changes with the name only data will be change.... and vlook up is not for one colum there is more. i just want to know which coding will help me to open any excle file from folder and do vlook up with first sheet with third sheet.

it shoud copy main file sheet one A14 cell till end and copy that in to macro file and do vlookup with main file sheet 3 with some colums.

 

[Luke M]

After you have the formulas, you can go to Data - Edit Links.

Click "Change source" to select the new file, and all formulas update automatically.