vlookup on a different workbook with a variable sheet name

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.

Please Login or Register  to view this content.

You are trying to use an A1 style reference in an R1C1 formula as well (C1:C6), Do you need the indirect/concatenate stuff as well? Try something like:

Please Login or Register  to view this content.

I thought the indirect/concatenate part was necessary but if it's not, I don't need it. I couldn't get your suggestion to work either though. On the actual spreadsheet, it translates into the following

=IF(A2<>"",IFNA(VLOOKUP(A2,"'[Excel Workbook 2014.xlsm]" & sheetname & "'!C1:H6",6,FALSE),0),"")

Well the indirect/concatenate stuff is only neccessary if you want the reference to be "hard-typed", so if you insert/delete rows around that range it will adjust itself.

Are you sure you copied it correctly, I just opened a new workbook, ran the code in post #3 and got:
=IF(A2<>"",IFNA(VLOOKUP(A2,'[Excel Workbook 2014.xlsm]April'!C1:H6,6,FALSE),0),"")

I was embedding that into my existing macro and it didn't work but when I created a new macro, it ran. However, it requires me to choose the file from the directory even though the file is already opened. Is there a solution to that? Thanks.