Cumulative sum on Excel tabs

[Lundin]

I start out a workweek with one tab for Monday's date. Each day I duplicate the tab and change the numbers. Then I start all over the next week. I would like to create a formula on Monday's worksheet that will sum up one cell. For instance, cell C22 would contain a formula that will automatically give the sum of cell B22 for the current tab and all previous tabs...no matter how many there are in the workbook. This would then give me the totals as I copy the worksheet day by day. Possible? Or is this just confusing?

 

[jbeaucaire]

Here's a User Defined Function to do it.

Option Explicit

Function SUMPREVSHEETS(RNG As Range)
'JBeaucaire  1/15/2010
'Add specified range in all sheets up to sheet the formula is on
Dim ws As Worksheet
Application.Volatile

For Each ws In Worksheets
    If ws.Index <= Application.Caller.Parent.Index Then _
        SUMPREVSHEETS = SUMPREVSHEETS + Application.WorksheetFunction.Sum(ws.Range(RNG.Address))
Next ws

End Function

==========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
========

Now, use it to add all the cells in a range on every sheet up to and including the sheet formula resides on like so:

=SUMPREVSHEETS(A1)

or

=SUMPREVSHEETS(A1:A10)