Copy formula when inserting row in specific columns

[TimvMechelen]

Hi all,

On a specific sheet (for example "sheet ABC") in a specific workbook (for example "Workbook ABC"), I have formulas in the columns D, E, G, J and I.

Is there a possibility, when inserting row(s), to automatically copy the formulas down in the inserted rows in the specific columns?

Thanks!

 

[Peter_SSs]

Are you inserting the rows manually or with vba?

 

[TimvMechelen]

Are you inserting the rows manually or with vba?

Manually.

 

[Peter_SSs]

Manually.

Then I don't think you can get your formulas in question automatically copied down.
However, you could have a macro that both inserts a row (or multiple rows) and copies the relevant formulas down. Is that something you would be interested in?

 

[Peter_SSs]

Is that something you would be interested in?

In case you are, you could try this in a copy of your workbook.
Select a row or rows or even a cell or cells in a contiguous area where you want the new rows inserted (e.g. you could select B10:D11 if you wanted 2 rows inserted below row 9) & then run the following macro.

Sub Insert_Rows()
  With Selection
    If .Areas.Count = 1 Then
      .EntireRow.Insert
      Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("D:E,G:G,I:J")).FillDown
    End If
  End With
End Sub

 

[TimvMechelen]

In case you are, you could try this in a copy of your workbook.
Select a row or rows or even a cell or cells in a contiguous area where you want the new rows inserted (e.g. you could select B10:D11 if you wanted 2 rows inserted below row 9) & then run the following macro.

Sub Insert_Rows()
  With Selection
    If .Areas.Count = 1 Then
      .EntireRow.Insert
      Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("D:E,G:G,I:J")).FillDown
    End If
  End With
End Sub

Thanks! it works

 

[Peter_SSs]

Thanks! it works

You're welcome. Thanks for the follow-up.