Getting the Sum of a Growing List

The following macro works on Excel 2003.
Your sums in the Total_Month_R row will automatically update as soon as you enter numbers in the new inserted cells.
I simplified your formatting lines to 1 line.

Please Login or Register  to view this content.

Hope this helps

How is the range specified in your formula? Can you copy your formula in your next post? It may have an impact on the way Exçel reacts.

Sure can here

=SUM(B2:B26), this is in B27

The same goes with C-M

Just an FYI this is the new code, just to make it look better.

Sub Add_Line()
'Selects DMs sheet
Sheets("Richard").Activate

'Selects the Last 2 Rows of sheet
Range("Total_Month_R").Select

'Cuts that and moves it down one
Range("Total_Month_R").Cut Destination:=ActiveCell.Offset(1, 0)

'Every thing from here down is to add the Borders
Range("Total_Month_R").Select
ActiveCell.Offset(-1, 0).Resize(1, 13).Borders.LineStyle = xlContinuous

'Asks for New Numbers
Dim result As String

result = InputBox("New Driver Number?", "Prompt Richard")
If result <> "" Then
Range("Total_Month_R").Select
ActiveCell.Offset(-1, 0).Value = result
End If
'Centers new data
Range("Total_Month_R").Select
ActiveCell.Offset(-1, 0).Resize(1, 13).HorizontalAlignment = xlCenter

End Sub

Change your total formula with this one and your macro will work just fine because this formula is self-adjusting to the number of rows in column A.

Please Login or Register  to view this content.

For column B it would be:

Please Login or Register  to view this content.

If you don't start at row 1 change A1 for A5 or whatever row you start at in the formula.

this sum works because it starts at cell B1.
Then the Row() function returns the cell position of the formula while the "INDEX(B:B,ROW()-1)" points to the cell just above it (Row()-1).

mmannning, 2 things...

1. Forum rules require to wait at least a day before bumping.

2. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)