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?

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

--
Don Guillett
SalesAid Software

"mac849" wrote in message
...
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?

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)


"mac849" wrote in message
...
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?

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" wrote:

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)


"mac849" wrote in message
...
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?

LOL. People can be so inconsiderate<g

Anyway, glad we helped.

Bob


"mac849" wrote in message
...
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" wrote:

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)


"mac849" wrote in message
...
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?