» Add values from the previous or next worksheet using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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
    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:

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