Unfortunately, this is the way Excel works, whether you insert manually or by
macro.
A work-around would be to always leave a row between your data and your sum
formula.
For example:
Data is in A1:A10.
There is a blank row at A11
Your sum formula is in A12 and is
=Sum(A1:A11)

Now, if you use the macro on row 10, the sum formula WILL change to reflect
the new range.
I know this isn't what you ideally are looking for but it will work.
Good Luck.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"sam_c" wrote:

Hi Gary

I have a question which is related to the code below.

I used this code and it worked out extremely well. I am using a macro button
'Insert Row' to insert rows and update formulae. I have a worksheet where the
data is in A1:D10. I am summing up columns in row 11 (e.g., sum(A1:A10). If I
select row 10 (or any cell on row 10) and insert a row using the button, the
formulae that sum up the columns dont get updated. (e.g., fomula for coumn A
should get updated to sum(A1:A11)).
Is there a way to do this?

Thanks in advance.



"Gary L Brown" wrote:

put this code in a standard module...

'/=======Start of Code==========================/
Sub InsertRowsAndFillFormulas()
'adds desired # of lines below the current line and
' copies the formulas to that/those lines
'added selection of more than one worksheet
' - Gary L. Brown
' - Kinneson Corp. 01/17/2001
' - modification from thread discussion in
' Microsoft.Public.Excel.Programming newsgroup
' on 01/17/2001
' Insert Rows -- 1997/09/24 Mark Hill
' The original macro is described in
' http://www.geocities.com/davemcritch...l/insrtrow.htm
Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long

' row selection based on active cell --
' rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number

If vRows = False Then Exit Sub
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line

ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0

'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.name).Select
i = i + 1
shts(i) = sht.name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), _
xlFillDefault
On Error Resume Next
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht

'reselect original group - Dave McRitchie 01/17/2001
Worksheets(shts).Select

End Sub
'/=======End of Code==========================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"jgray" wrote:

hey guys,

I'm trying to create a macro to streamline a process for one of our factory
guys. Right now, I'm going down and manually inserting rows into a database
and then dragging the needed formulas and information from the rows above
down. What I'm trying to do is make it so he can place his cursor, hit the
macro key stroke and have that done for him. When I record the macro, I
think I am making an absolute cell reference, because when I run the macro it
will only instert where I initially inserted the row. How do I create the
macro so he can select where he wants it and then have the row inserted and
formulas dropped down for him?

Thanks in advance for all your help!

James