Looking up matching columns from different files

[arham_1987]

Hi,

I am working on a file which is a consolidation of different sources, after this process I have to add columns from other sub-files by either vlookup or index formula in VBA. My question is, how to get the below formula in a loop so that "Book1" and "Sheet1" are picked up from a list of books and sheets that I have on my source file and loop through this list?



ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-18],'[Book1]Sheet1'!C1:C22,21,FALSE)


Any advice is highly appreciated.

Thanks,
Arham

 

[arham_1987]

Any suggestion guys?