It's possible to add values from a range in another worksheet like this:

=SUM(Sheet1!A1:A100)

This formula could then be copied to the other sheets in the workbook, but you would have to edit all the sheet names manually. With the user defined functions below you can enter the function once (e.g. if you group the worksheets first) and skip that manual editing of the worksheet name in the formula:

Function SumPreviousSheet(InputRange As Range) As Double
' adds the values in InputRange in the previous worksheet
' returns 0 if the function is used in the first worksheet
    Application.Volatile ' omit if not necessary
    SumPreviousSheet = 0
    On Error Resume Next
    SumPreviousSheet = Application.WorksheetFunction.Sum(InputRange.Parent.Previous.Range(InputRange.Address))
    On Error Goto 0
End Function

Function SumNextSheet(InputRange As Range) As Double
' adds the values in InputRange in the next worksheet
' returns 0 if the function is used in the last worksheet
    Application.Volatile ' omit if not necessary
    SumNextSheet = 0
    On Error Resume Next
    SumNextSheet = Application.WorksheetFunction.Sum(InputRange.Parent.Next.Range(InputRange.Address))
    On Error Goto 0
End Function

You can also create a function that lets you add in another worksheet like this:

Function SumOffsetSheet(InputRange As Range, Optional SheetOffset As Integer = 0)
    Application.Volatile ' omit if not necessary
    SumOffsetSheet = 0
    On Error Resume Next
    SumOffsetSheet = Application.WorksheetFunction.Sum(Worksheets(InputRange.Worksheet.Index + SheetOffset).Range(InputRange.Address))
    On Error Goto 0
End Function

Or you can create a function that lets you add in another worksheet like this:

Function SumIndexSheet(InputRange As Range, Optional SheetIndex As Integer = 0)
    Application.Volatile
    SumIndexSheet = 0
    If SheetIndex = 0 Then
        SumIndexSheet = Application.WorksheetFunction.Sum(InputRange)
    Else
        On Error Resume Next
        SumIndexSheet = _
            Application.WorksheetFunction.Sum(Worksheets(SheetIndex).Range(InputRange.Address))
        On Error Goto 0
    End If
End Function

Examples:
Add the values in the range A1:A100 in the previous worksheet: =SumPreviousSheet(A1:A100)
Add the values in the range A1:A100 in the next worksheet: =SumNextSheet(A1:A100)
Add the values in the range A1:A100 in the next worksheet: =SumOffsetSheet(A1:A100,1)
Add the values in the range A1:A100 in the previous worksheet: =SumOffsetSheet(A1:A100,-1)
Add the values in the range A1:A100 in the second worksheet: =SumIndexSheet(A1:A100,2)
Add the values in the range A1:A100 in the active worksheet: =SumIndexSheet(A1:A100)

Like this post