'Function to shift down formulas

I have a few formulas in the 1st row of a random table. I am trying to shift down / copy them to multiple rows beneath. Some of the formulas are copied, some of them remain empty.

Original Table

I tried resizing, but it doesn't work with a table.

Resized Table - The headers were copied, the formulas removed.

Sheets(destinationSheet).Rows(startrow + 1 & ":" & startrow + 2).Insert Shift:=xlDown

Formulas get copied in some of the columns and for the others I get blank cells.

I also tried with different code (within a loop for each row):

ActiveSheet.Rows(.HeaderRowRange.Row + .ListRows.Count + 1).FillDown '1st example
ActiveSheet.Rows(.HeaderRowRange.Row + .ListRows.Count + 1).Insert '2nd example

.Resize (ActiveSheet.Range(.HeaderRowRange(1, 1), Cells(.HeaderRowRange.Row + .ListRows.Count + 1, .ListColumns.Count + 1)))

The 1st example copies the formulas correctly for all of the columns, but overwrites the rows below (doesn't create a new row each time).
The 2nd example create a new row in the table, but copies only some of the formulas.

New sample table comparison

excel vba resize

Solution 1:^[1]

Maybe try something like this then :

The faster way would be :

Range("A1:A10").Formula = "=C1+B1"

Something slower..

Range("A1").Formula = "=C1+B1" 'Replace by your formula
Range("A1").Copy
Range("A1:A10").Pastespecial(XlPasteall)

You can alsoe use FillDown :

Range("A1").Formula = "=C1+B1"
Range("A1:A10").FillDown

Note : that the faster way would be the first proposition because which autofills (FillDown) the range with the formula.

Edit In your case this would work perfectly :

ActiveSheet.ListObjects("Tableau1").DataBodyRange.FillDown

Change Tableau1 By your Tab Name.. Here DataBodyRange is used to select your table data.

Edit 2 Without using DataBodyRange

Sub SelectTableBody()
    Dim rTableData As Range

    With ThisWorkbook.Worksheets(1)
        Set rTableData = .ListObjects("Table1").DataBodyRange
        Set rTableData = rTableData.Offset(0, 1) _
          .Resize(, rTableData.Columns.Count - 2)
    End With

    rTableData.FillDown
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'Function to shift down formulas

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]