Autofill Formula Across Table with Variable Number of Columns & Rows

WiggySlim

New Member
Joined
Dec 19, 2013
Messages
2
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:

WeekPerson #1 Person #2
#1
#2
#3
#4
#5
#6
#7
#8
=IFERROR(ROUND(SUM(LOOKUP(2,1/([Week]<>""),[Week]))-A2,1),"")

<tbody>
</tbody>

Code:
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top