Automatically move a formula line down when new data is added

Copy and paste the code to the worksheet module that contain the sum formula

Please Login or Register  to view this content.

Here is a sample file that I worked on. Not exactly sure if this is what you need.

thanks Jen. How could I expand this for all worksheets (Col A to Col J)?
For example, the formula works perfectly in the A column of sheet 1, but how would I make it accessible by all sheets in a workbook from Column A to J?

Thanks

Also, I am trying to get everything below the formula line to also move down with the formula.
Thanks

jeffreybrown,

This formula should avoid the circular reference. I realize this still doesn't solve the OP's problem, but I figured I'd provide my useless input.

=SUM(A1:INDEX(A:A,ROW()-1))

This works too, but i am trying to figure out how to lock the row above it and display the formula and everything below the A12 line.

=SUM(A$1:A11)

Try this:

Please Login or Register  to view this content.

This will only grow. It will not shrink if values are deleted.

Whizbang: The code was not running for some reason. It was giving an error message.

Thanks

What was the error. What change did you attempt? Please provide more information on your error. I just realized that the above code does not check the size of Target, and so might throw an error if multiple cells are changes at once.

Is there a way to edit this code to add data between the SummaryRow?

I tried to Set a range but I there seems to be an issue and it is not pasting between the SummaryRow.
Set rTo3 = .Range("SummaryRow").Offset(-1).EntireRow.Cells(1, 1).Resize(1, lRightCol)

Thanks

Please Login or Register  to view this content.