How do I reference values from 200 worksheets onto a summary sheet

[Guest]

I'm using Excel 2003. I have a workbook with over 200 worksheets. (Each tab
named for an individual in a group.) I'm trying to set up a summary sheet
which needs total values from each of the individual worksheets. I'm using a
macro which creates a Table of Contents, then sorts the sheets and the list,
providing a link to each sheet. I expect new names to be added so the macro
will have to be rerun occasionally. A separate macro places the sheet name
in cell A1 of each sheet in case that can be of any use. Is there a way to
get the total values from each sheet onto the summary page without having to
write or modify 200 formulas?

 

[Don Guillett]

set up a dummy firstsheet and a dummy lastsheet and add your new sheets in
between
=sum(firstsheet:lastsheet!a2)

 

[Bob Phillips]

Here is one way with event code. Just change the B12 to your cell of choice,
and the Summary sheet name from Summary


Private Sub Workbook_Open()
SheetSummary
End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
If sh.Name = "Summary" Then
SheetSummary
End If
End Sub

Private Sub SheetSummary()
Dim sh As Worksheet
Dim i As Long
i = 1
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Summary" Then
Worksheets("Summary").Cells(i, "A").Value = sh.Name
Worksheets("Summary").Cells(i, "B").Formula = "='" & sh.Name &
"'!B12"
i = i + 1
End If
Next sh
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

This worked great. (Actually, I got an error until I realized one of the tab
names had an apostrophe in it. She's now named OConnor.) Plus I got a
lesson in workbook event code. Thanks!

 

[Bob Phillips]

LOL. People can be so inconsiderate<g>

Anyway, glad we helped.

Bob