Need help on code to copy formula from one cell to other cells in the row on a dynamic table

roosechua

New Member
Joined
Sep 25, 2019
Messages
1
Hi All,

I am still learning VBA and currently, I am stuck on how I can copy formula from cell(1,1) on a table to new rows when the table expands (or new columns are added). Here's an example of my setup:

Table1: PricesPerFruit
[TABLE="class: outer_border, width: 200, align: left"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]pineapple[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]grapes[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Table2: ItemsSoldperWeek (Initially 1 column)
[TABLE="class: outer_border, width: 100, align: left"]
<tbody>[TR]
[TD]Wk1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Table3: SalesPerWeek (Initially 1 column)
[TABLE="class: outer_border, width: 100, align: left"]
<tbody>[TR]
[TD]Wk1[/TD]
[/TR]
[TR]
[TD]$5[/TD]
[/TR]
[TR]
[TD]$20[/TD]
[/TR]
[TR]
[TD]$12[/TD]
[/TR]
</tbody>[/TABLE]

The formula in cell(1,1) on the SalesPerWeek table is
Code:
=PricesPerFruit[[Price]:[Price]]*ItemsSoldPerWeek[@Wk1]
.

However, when I try to add new column for the new week, the formula is not copied/autofilled on the next columns. I am trying to populate these new columns and my code so far:

Code:
Sub CopyFormulaToNewColumns()    Dim ws As Worksheet
    Dim tblFruits As ListObject
    Dim tblQuantity As ListObject
    Dim tblSales As ListObject
    Dim lc As Long
        
    Set ws = Worksheets("Sheet3")
    Set tblFruits = ws.ListObjects("PricesPerFruit")
    Set tblQuantity = ws.ListObjects("ItemsSoldperWeek")
    Set tblSales = ws.ListObjects("SalesPerWeek")


    lc = tblSales.Range.Columns.Count
    
    For counter = 2 To lc
        tblSales.DataBodyRange.Cells(1, 1).Select
        Selection.AutoFill Destination:=tblSales.DataBodyRange.Cells(1, counter), Type:=xlFillDefault
    Next
    
End Sub

Unfortunately, when I run this code, it gives me the error message "Run-time error '1004': AutoFill method of Range class failed".
Adding rows has no problem, as somehow, the new row is autofilled.

Anyone whom can help to give me a hint on how to do this? My full code actually creates a number of new columns at once, and I need to make sure that the formulas are set on these new columns. Thanks in advance for the help!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
use the Formula property of a range:
Code:
[COLOR=#333333]tblSales.DataBodyRange.Cells(1, counter).formula = [/COLOR][COLOR=#333333]tblSales.DataBodyRange.Cells(1, 1).formula[/COLOR][COLOR=#333333]
[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,595
Members
452,656
Latest member
earth

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