Solved

# Sum range for all worksheets starting with a specifc name

Posted on 2010-08-27
Medium Priority
427 Views
In Worksheet.Name of "Main" in cell "K1", I need a formula that will give me the sum of all ranges A11:B12 on all worksheets with a name like "Series*".

If my sheet names for "Series" remained static, I could use a formula like this:
=SUM(Series1_AA:Series9_AA!A11:B12)+SUM(SeriesA_AA:SeriesZ_AA!A11:B12)

But the problem is, these sheet names change regularly.  So one day my formula may need to be:
=SUM(Series1_BB:Series9_BB!A11:B12)+SUM(SeriesA_BB:SeriesZ_BB!A11:B12) OR
=SUM(Series1_CC:Series9_CC!A11:B12)+SUM(SeriesA_CC:SeriesZ_CC!A11:B12)

The one thing that remains consistent is the "Series" portion of the name.  I'm guess there a way that this can be done through VBA.  I'm open to any suggestions.  Thanks!

0
Question by:KP_SoCal
• 3
• 2

LVL 17

Accepted Solution

calacuccia earned 2000 total points
ID: 33544765
Put this formula where you want the result

=SeriesSum(AA11:B12)

And create this VBA Function

Function SumSeries(myRange As Range) As Variant
Dim rStr As String
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 6) = "Series" Then SumSeries = SumSeries + Application.WorksheetFunction.Sum(sh.Range(rStr))
Next sh

End Function

0

Author Closing Comment

ID: 33544931
Great!  Just what I needed, thanks so much!!!
0

LVL 17

Expert Comment

ID: 33545232
KP_SoCal:

You're welcome, thanks for the grade.
0

LVL 81

Expert Comment

ID: 33546814
I realize that the question has already been answered, but you will probably encounter problems getting the user-defined function to refresh its results. If a user changes the name of a worksheet or inserts a new one into an existing workbook, the function will not update.

Workaround #1: Make the function volatile. Add the following statement right after your Dim statements:
Application.Volatile             'Function will recalculate after any change in any cell values. May make workbook sluggish if you use it in many places.

Workaround #2: Add a dummy parameter, and change the value of that parameter whenever you want the function to recalculate.
=SeriesSum(AA11:B12, \$Z\$1)                       'Z1 contains a dummy parameter which is not used in calculations. Changing it forces SeriesSum to recalculate

``````Function SumSeries(myRange As Range, Dummy As Variant) As Variant
Dim rStr As String
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 6) = "Series" Then SumSeries = SumSeries + Application.WorksheetFunction.Sum(sh.Range(rStr))
Next sh

End Function
``````
0

Author Comment

ID: 33546910
Brad, this is outstanding.  I'm going to go with workaround #2 to avoid using the volatile function due to the size of my actual spreadsheet.  I really appreciate this extra bit of information.  Have a great weekend and thanks again for the extra mile on this. ;-)
0

LVL 17

Expert Comment

ID: 33547166
Thanks for follow-up Brad, I should have added that myself, but was to much in a hurry and it's been a while since I created a UDF :-)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.