Insert multiple rows including formulas

Question

I have an Excel Sheet in which the user should be able to insert multiple rows which automatically include the formula from the above row. I have programmed the following formula for a single row:

Sub Insert_Single_Row()
ActiveCell.EntireRow.Copy
Tabelle3.Range("A28").EntireRow.Insert
End Sub

However, I want to insert more than one row so I found this formula:

Sub Insert_Multiple_Rows()
   Dim CurrentSheet As Object
    For Each CurrentSheet In ActiveWindow.SelectedSheets
    CurrentSheet.Range("A28:A50").EntireRow.Insert
    Next CurrentSheet
End Sub

This formula inserts a multiple number of rows. However, it does not copy the formulas from the row above the inserted rows.

Do you guys have any idea how to combine those codes so once I insert a mutliple number of rows into the sheet the formulas from the row above the inserted rows is copied in all of them?

Thanks for any help.

Why is Range("A28:A50") hard coded? Do yu always want to insert at that location? — chris neilsen
Sorry. The range should be hardcoded but not "A28:A50". Instead it should be "28:50" since the entire row with all formulas from above should be copied and not only from column A. — Michi

Sun Sun · Accepted Answer · 2016-06-15T07:27:23

Sticking to the code you already had, this should do the trick:

Sub Insert_Multiple_Rows()

Dim CurrentSheet As Object

    For Each CurrentSheet In ActiveWindow.SelectedSheets

        CurrentSheet.Range("A28:A50").EntireRow.Insert
        CurrentSheet.Range("A27:A50").EntireRow.Formula = CurrentSheet.Range("A27").EntireRow.Formula

    Next CurrentSheet

End Sub

Insert multiple rows including formulas

1 Answers