Apply Vlookup formula in all the available sheets in a workbook

[Deysam]

Hi Alan,

I am using your code quite well with the previous file names eg Book4_Dec 2019 etc. but, the exact file names are as per the attached (MSCI Equity Index Constituents 20191130)and the code doesn't works would you please suggest any workaround.

the debugger shows

 ThisMonthsLatestBook.Worksheets.Add After:=ThisMonthsLatestBook.Worksheets.Item(Worksheets.Count)

Also, I tried to edit the vlookup to get the output but it didn't worked

.Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value = "=VLOOKUP(" & outputSheet.Name & "!$A2,'" & sourceBook.Path & "\" & "[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"

the formula should be the one in the code above minus (-) cell value in the range $P. I tried using -$P and also Range $P but, it didn't worked.


Thanks a lot agin