VBA with XLOOKUP using different Tables

[gberg]

I am trying to come up with a VBA formula that pulls table names from different tables based on other criteria. I can get the table names but I am having issues making the name work with a XLOOKUP formula.

Here is a formula where I just use the table name itself

ActiveCell.FormulaR1C1 = _
"=XLOOKUP([@[PROJ '#]],Table71[PROJ '#],Table71[TOTAL JGP],""N/A"",0)"

Here is what I have tried

'Get Previous Month Table Name
Sheets(PM_tab).Select
Range("A2").Select
Dim TableName_PM_JPR As ListObject
Set TableName_PM_JPR = ActiveCell.ListObject
Sheets(JPR_tab).Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[-24],TableName_PM_JPR[PROJ '#],TableName_PM_JPR[TOTAL JGP],""N/A"",0)"


The macro throws an error on the XLOOKUP formula. I know the table name "TableName_PM_JPR" is correct, I tested by pasting the value in a worksheet. Just not sure what I am doing wrong.

Thanks for any help

 

[gberg]

Peter, thank you so much! This has been much appreciated. You not only taught me a few things, how to put VBA into the forum and the best way to copy code, that has been very helpful and have also solved my issues with the VBA code itself! Good stuff!

Thanks,

Greg

 

[Peter_SSs]

You're welcome. Glad we got there in the end.