Autofill Formula Across Table with Variable Number of Columns & Rows

[WiggySlim]

I'm trying to create a Macro in VBA that will autofill a formula in the first column of the total row of a table over to a newly created column all the way to the right. Here's what I have right now:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Person #1 [/TD]
[TD]Person #2 [/TD]
[/TR]
[TR]
[TD]#1 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#2 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IFERROR(ROUND(SUM(LOOKUP(2,1/([Week]<>""),[Week]))-A2,1),"")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub Add_Person()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Weight")
    tbl.ListColumns.Add.Name = "New Person"
    Range("Weight[[#Totals],[Week]]").Select
    Selection.AutoFill Destination:=Range("Weight[#Totals]"), Type:= _
        xlFillDefault
    Worksheets("Weight Log").Cells.EntireColumn.AutoFit
End Sub

The issue I'm running into is that the formula in the first column of the total row of my table isn't changing to reference the new column it's being copied over to. The first column is titled "Week", the second column "Person #1 ", and the third column is "Person #2 ". As it autofills from the first column, the formula should change to reference the column titled "Person #1 ", "Person #2 ", etc.

To add another wrinkle into this, the total row is subject to change as new rows will get inserted as time goes on.

Thank you in advance for your help.

 

[AFPathfinder]

Have you tried Range.FormulaR1C1 instead of the Range.AutoFill?

In my limited experience with AutoFill in VBA, relative referencing works. If I had to take a shot in the dark, I'd say the named ranges might be causing issues.