VBA: Referring to a table name variable within formula code

[le_robert]

Hello,



I have written a macro to split a large table into several smaller ones on separate worksheets. Once this is done, I want to put the same two sumif formulae in the same cell in each sheet. My current code, which works, uses columns as references:

For Each ws In Sheets

    ws.Activate
        Range("A1:A6").EntireRow.Insert
        Range("F3").Formula = _
        "=sumif(E:E,""<>Translation"",H:H)"
        Range("F4").Formula = _
        "=sumif(E:E,""Translation"",H:H)"
        Range("E3").Value = "Proofreading"
        Range("E4").Value = "Translation"
    
    With Range("E3:F4")
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .BorderAround Weight:=xlMedium
    End With
    
    Range("A1").Select
    
Next ws

However, I would like to be able to reference the named range within the table, the formula for which (based on a recorded macro) would be something like:

    Range("F3").FormulaR1C1 = _
        "=SUMIF(TABLENAME[[#All],[Issue Type]],""<>Translation"",TABLENAME[[#All],[Word count]])"

Is it possible to define a variable, such as ActiveSheet.ListObjects(1), and inserting the variable into the formula so that when the loop goes to each sheet, it does the calculation based on the table in that sheet (replacing the placeholder "TABLENAME" in my example code? I've tried several different things and none seem to work. If it's of any use, I've made it so the table on each sheet has the same name as the sheet.

Thanks in advance.