VBA formal using a table name refrence

[gberg]

I have a spreadsheet that has multiple tabs. I need a VBA formula that will use the table name on each tab that it is run on.

I have this to get the table name of the table on each tab
Range("A3").Select
Dim tName As String
tName = ActiveCell.ListObject.Name



Here is an example of the formula I want (it has "Table19" which is what I want to replace with "tName" from above)

Range("E858").FormulaR1C1 = "=SUMIF(Table19[PROD],[@[PRODUCT LINE]],Table19[WEEKLY PAYROLL])"

I tried this but it just gives me an error

"=SUMIF(tName & "[PROD]",[@[PRODUCT LINE]],tName & "[WEEKLY PAYROLL]")"

Any help would be appreciated

Thanks,

Greg

 

[gberg]

I think I figured it out

'Get Table Name
Range("A3").Select
Dim tName As String
tName = ActiveCell.ListObject.Name

Range("E858").FormulaR1C1 = "=SUMIF(" & tName & "[PROD],[@[PRODUCT LINE]]," & tName & "[WEEKLY PAYROLL])"