MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Evaluating multiple worksheets in a function

Posted by Lisette Villavicencio on May 20, 2000 7:42 AM

Hi! I will use an example to explain my question.
I have a workbook with 11 worksheets, 10 of them have
data and the last one is a resume. I want in the
resume to sum every value in cell C:3 of every
worksheet (from 1 to 10) if value in C:2 equals 2.

Please help me!!!!!

Posted by Celia on May 21, 0100 12:09 AM


There must surely be a way of creating a single formula with Excel’s worksheet functions but here are some other ways.
It is assumed that your resume sheet is called “Summary”

Without VBA :-

In cell C4 of each sheet except the Summary sheet enter this formula :-
This puts the C3 value in C4 if the C2 value is 2.
In a cell on the Summary sheet enter a formula to sum all of the other sheets’ C4 cells.

With VBA :-

Both methods below will put the total required in cell C3 of the Summary sheet and will work for any number of sheets but the first sheet must be the Summary sheet.

First method :-
Put the following macro in a normal module. The macro has to be run to update the total on the Summary.

Sub SumSheets()
Dim C As Integer, I As Integer
Dim sumSheet As Worksheet
Set sumSheet = Sheets("Summary")
If Sheets(1).Name <> sumSheet.Name Then
MsgBox "Move the Summary sheet so that it is the first sheet"
Exit Sub
End If
C = Worksheets.Count
For I = 2 To C
If Sheets(I).Range("C2") = 2 Then
sumSheet.Range("C3").PasteSpecial Paste:=xlValues, operation:=xlAdd
End If
Application.CutCopyMode = False
End Sub

Second method :-
Put the following in the Summary sheet code module. The total will be updated each time the Summary sheet is selected.

Private Sub Worksheet_Activate()
Dim C As Integer, I As Integer

Posted by Lisette Villavicencio on June 23, 0100 5:39 AM

Making a range for worksheets

Hi Celia!
I want to know how I can simplify the formula in the Summary (Without VBA) so I don't need to write the name of all the sheets.
Is like making a range:
***!B2:^^^^!B2 this means from evaluate cell B2 in every sheet between *** and ^^^^