One way:

Put a blank worksheet named First before the sheets you want to add, and
a blank worksheet name Last after the last sheet you want to add. Then
use

=SUM(First:Last!H1000)

Make sure any added sheets fall between First and Last.

Or, if you want a programming solution, perhaps something like this User
Defined Function:

Public Function SumOfAllSheets(ByVal rRef As Range) As Variant
Dim ws As Worksheet
Dim dTemp As Double
Dim sAddress
Application.Volatile
On Error GoTo ErrHandler
sAddress = rRef.Address
If sAddress = Application.Caller.Address Then
SumOfAllSheets = CVErr(xlErrRef)
Else
For Each ws In Application.Caller.Parent.Parent.Worksheets
dTemp = dTemp + ws.Range(sAddress)
Next ws
SumOfAllSheets = dTemp
End If
Exit Function
ErrHandler:
SumOfAllSheets = CVErr(xlErrValue)
End Function


In article . com,
"Krayten" wrote:

Hi,

I need to add together the values of cell H1000 in every sheet in the
workbook.
The trouble is each day there might be a different number of sheets.

Ideally the total value would be returned as a variable so that I can
use it deeper
in for a further calculation, which I just about can manage!

Would be grateful for any help/pointers any kind soul can offer.

Thanks,

Stuart