Hi All I am trying to write a VLOOKUP formula in a macro run from a template report to Return data from yesterdays report (which started life as the template then saved as that days report). The normal VLOOKUP formula works fine normally when both reports are open. I wanted to automate the process as much as possible which is why I wanted to incorporate the formula in a macro. The date in the file is displayed as "Report 19.12.19.xls". When I run the macro it stops with a compile error: Sub or Function not defined and highlights TODAY in the formula. Can anyone help please?
VBA Code:
' ActiveCell.FormulaR1C1 = _
' "=VLOOKUP(C12,INDIRECT("[Offline Report_"&TEXT(TODAY()-1,"DD.MM.YY")&".XLS]Offline Tuesday to Friday Repor'!C12:C17,6,FALSE))