For Dave Hawley regarding 15492.html

Add two blank worksheets - one in front of the first sheet you want to include in the total and the other at the end (you can name them something like Start and End). Use these sheet names in your formula and make sure that new sheets are only added between the Start & End sheets. Your formula will then not be affected by adding and deleting sheets.

Yes, thank you Anon. It will also work to just not move tabs around. My question was more "standard practices" rather than tutorial. :)

I think having blank start and end sheets is "standard practice".
If you hard code each sheet as you suggest in your formula, you cannot avoid having to revise your formula when sheets are added or deleted.
Using blank start and end sheets avoids this and also you can do anything you like (move, add, delete) between the two blank sheets without having to change the formula.

Hi Kevin

Firstly, Thankyou for the kind words!

Asa annon has suggested adding two blank worksheets would help. I myself would prefer to use a Custom function, like the one I have written below.

Function SumAllSheets(SumRange As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''''
'Sum the chosen range on ALL sheets EXCEPT the _
Sheet that houses the formula.
'''''''''''''''''''''''''''''''''''''''''''''
Dim Wst As Worksheet
Dim SThisSheet As String
'forces a recalculation if a sheet is added
Application.Volatile
'Pass the activesheet name to a String
SThisSheet = ActiveSheet.Name

For Each Wst In ActiveWorkbook.Worksheets
'Sum the chosen range on all sheets Except _
'Sheet(SThisSheet)
If Wst.Name <> SThisSheet Then
SumAllSheets = WorksheetFunction.Sum _
(Wst.Range(SumRange.Address)) + SumAllSheets
End If

Next Wst

End Function

'To put this function in push Alt+F11 and then go to Insert>Module. Paste in the code above. Now push Alt+Q to return to Excel.

Select the cell on you "Total" sheet that you want the total to appear in. Now push Shift+F3 and scroll down to "User Defined Functions" then select "SumAllSheets". Use it as you would the normal sum function ie; select the range you want to sum (but only on the one sheet). You will end up with a simple formula like:

=SumAllSheets(A1:A4)

This will no automatically sum range A1:A4 on ALL sheets EXCEPT the one the formula is on. Each time you add a sheet now it will incorporate it. You may need to push F9 to force a recalculation.

Dave

OzGrid Business Applications

Well, what can I say? If I were to attach a firewire to both our brains and try downloading all you're wealth of knowledge, we'd probably have a mess all over the walls -- my brains.

I took to BASIC back in the early 1980's like a duck in water. I don't know why, but for some reason Visual Basic makes some quantum leaps in logic that I find hard to follow. I've dabbled with it for years now, but to sit down and write from scratch, I just can't do.

On the flip side, I have amazed my peers with what I'm able to accomplish with formulas.

I have made notes on several postings you've made both regarding formulas and VBA. I plan on using them to further enhance my skills.

Thanks

LOL! I have had my brains and dynamite mentioned in the same sentence before, but never in such a nice way :o)

I would say that, if you could get your head aound BASIC, VBA should be a walk in the park. Just forget everthing they taught you in BASIC :o)

I used to be able to achieve almost anything with Worksheet formulas, then I took to VBA and never looked back. I must admit though learning Excel from the bottom up helps me no end in my VBA coding. As I'm so familiar with the interface of Excel, I can, 9 times out of 10 avoid those painfully slooooowwwww Loops.

There is lots more tips, tricks and VBA code on my Website all free fo the taking as well!

Dave
OzGrid Business Applications

I disagree with :-
"prefer to use a Custom function"

Not necessary to get involved with VBA when simple worksheet functions can do the job.