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