G

#### Guest

use SumProduct that refers to cells on the current sheet and cells on another

sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006

Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other

sheets. When I insert a row, I need to update the formulas starting with the

inserted row down to the last row to update the formulas for the other

sheets. But since the last row address in the BFP Range changes, I cannot

figure out how to get the row address to copy the formula down to. I'm

probably making this too hard, but here is the macro I started, before I came

up with the range name.

Sheets("Growth Rates").Activate

Rows(RowNum).Select

Selection.Insert Shift:=xlDown

Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select

Selection.Copy

Range("A" & RowNum).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select

Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E"

& RowNum), Type:=xlFillDefault

Range("C" & RowNumM1 & ":" & "E" & RowNum).Select

I used an input box to get the RowNum where the user wants to insert the new

row.

RowNumM1 is the RowNum - 1

I'm trying to get the RowNum of the last row in the range (I know I need

another variable name) for the Selection.AutoFill Destination or maybe there

is a more simple way. Any help would be appreciated.