computing multiple sheets

[Xception]

I would like like to make a macro which computes multiple values spread over sheets kinda like this

Sheet 1 Sheet 2 Sheet 3
1 + 2 = 3

But you don't know how many sheets there are because you can add more sheets so how can i do this with a macro or maybe just a function?

 

[rmikesmith]

Xception,

The following function will do what you want. It can be used as a worksheet function as well as called from VBA. It assumes that the total is computed using the same cell accross all worksheets. It takes two arguments, the cell to use in the calculation, and a boolean (TRUE/FALSE) telling the function whether to exclude the worksheet from which it is called.

Function SumAllSheets(ByVal InCell As Range, ByVal ExcludeCurrentSheet As Boolean)
Dim wks As Worksheet
Dim Sum As Variant

  SumAllSheets = CVErr(xlErrRef)
  If InCell.Count > 1 Then Exit Function
    
  If ExcludeCurrentSheet Then
    For Each wks In ThisWorkbook.Worksheets
      If wks.Name <> InCell.Parent.Name Then
        Sum = Sum + wks.Range(InCell.Address)
      End If
    Next wks
  Else
    For Each wks In ThisWorkbook.Worksheets
      Sum = Sum + wks.Range(InCell.Address)
    Next wks
  End If
  SumAllSheets = Sum
  
End Function

HTH
Mike