Insert New Rows And Copy Formula Automatically

[nehpets12]

Just a quick question on Excel. I have created a new report which has a number of formulae in it. There will be a requirement by the Depots to insert rows. How do I set it up to the formula in the respective rows automatically is copied into the new cells when the rows are added?

 

[GIngerlomax]

Yes its old but i am trying to get my head around a similar problem. Could he have not just converted it to a table?

 

[PL dub]

I'm using the following code. It will insert a new row and copy the formula in Column F, O, P, Q, R, S.

Sub InsertRow_and_ColBformula()
With ActiveCell.EntireRow
.Insert
.Cells(1, 5).Copy .Cells(0, 5)
.Cells(1, 15).Copy .Cells(0, 15)
.Cells(1, 16).Copy .Cells(0, 16)
.Cells(1, 17).Copy .Cells(0, 17)
.Cells(1, 18).Copy .Cells(0, 18)
.Cells(1, 19).Copy .Cells(0, 19)
End With
End Sub

Now I would like to manually introdure a variable (with a msg box or something similar) to insert 30 or 31 row depending of the numbers of days in the month. Than add dates in a descending order in Column A of these new rows.....

Any help would be appreciate.


Another option would be to insert one row only and adding the date of the day before in Column A of the new row.


Additionnaly, I would like to modify my code above to use a determine row where to insert inlieu of using ActiveCell.

 

[PL dub]

I change my code to this

Sub InsertRow_TEST_with_FORMULA()
Sheets("DATA").Select
Range("A5").Select
With ActiveCell.EntireRow
.Insert
.Cells(1, 5).Copy .Cells(0, 5)
.Cells(1, 15).Copy .Cells(0, 15)
.Cells(1, 16).Copy .Cells(0, 16)
.Cells(1, 17).Copy .Cells(0, 17)
.Cells(1, 18).Copy .Cells(0, 18)
.Cells(1, 19).Copy .Cells(0, 19)
End With
End Sub

Now I would like to manually introdure a variable (with a msg box or something similar) to insert 30 or 31 row depending of the numbers of days in the month. Than add dates in a descending order in Column A of these new rows.....

Any help would be appreciate.


Another option would be to insert one row only and adding the date of the day before in Column A of the new row.