Microsoft Excel - Automatically Extending Formulas

[Cadillac Williams]

Hi,

I have a large worksheet that has a number of fairly complex formulas that
are repeated in each row with only the row number reference changing. When I
insert a new row in between two existing rows that contain formulas I would
like excel to automatically insert the formulas I have in the row above it
into my new row. Excel already does this with my formatting, I want to know
how to do it with my formulas as well.

 

[Michael]

Hi Cadillac. If you copy the entire row, above the one where you are going
to insert a new row, and then paste insert row, the formulas will be copied
and the cell references will change. HTH

 

[Don Guillett]

How about if you put your formulas in row 1>hide the row>double click

right click sheet tab>view code>insert this. Now, when you double click
anywhere on the sheet row will be inserted before the last row and row 1
will be copied.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(lr).Insert
Rows(1).Copy Rows(lr)
End Sub