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

[roosechua]

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

Fruit	Price
apple	1
pineapple	2
grapes	3

<tbody>
</tbody>

Table2: ItemsSoldperWeek (Initially 1 column)

Wk1

5

10

4

<tbody>
</tbody>

Table3: SalesPerWeek (Initially 1 column)

Wk1

$5

$20

$12

<tbody>
</tbody>

The formula in cell(1,1) on the SalesPerWeek table is

=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:

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!

 

[sijpie]

use the Formula property of a range:

[COLOR=#333333]tblSales.DataBodyRange.Cells(1, counter).formula = [/COLOR][COLOR=#333333]tblSales.DataBodyRange.Cells(1, 1).formula[/COLOR][COLOR=#333333]
[/COLOR]