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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top