Hi,

I just started venturing into VBA so please excuse my ignorance. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code. Can someone please help to see what is wrong with the below syntax or if there's a better solution to this? Much appreciated.

range("E2").Select
    sheetname = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmmm")
    
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-4]<>"""",IFNA(VLOOKUP(RC[-4],indirect(concatenate(""'[Excel Workbook 2014.xlsm]"",sheetname,""'!C1:C6"")),6,false),0),"""")"