Sum of cells spanning several worksheets
 

I have a workbook that will have a varying number of
worksheets. The worksheet names will be "list (1)"
thru "list (xx)". Each worksheet has several cells that
that use "conditional sum" function to total quantities of
items based defined conditions.

I'd like to have a place on "list (1)" that will provide
total quantities that span all "lists", but I'm not sure
where to start.

I think I want to count the number of worksheets
named "list (*)", then, if "list (4)" exists, add the
value of the specific cell, if not, add zero. Am I on the
right track, or is there a better way? Can someone show
how to get started? Point me in the right direction and
I'll run with it.

THANKS!
Rich

Sum of cells spanning several worksheets
 

Option Explicit

Sub Main()
'puts a formula in sheet List(1), cell A1 that
'sums cells A2 for all sheetss named like "list(*)".
Dim wks As Worksheet, str As String
For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) Like "pist(*)" Then
Let str = str & "'" & wks.Name & "'!A2 + "
End If
Next wks
If Len(str) 0 Then
Worksheets("List(1)").Range("A1").Formula = _
"=" & Left(str, Len(str) - Len(" + "))
End If
End Sub

--
Bob Kilmer
"Rich" wrote in message
...
I have a workbook that will have a varying number of
worksheets. The worksheet names will be "list (1)"
thru "list (xx)". Each worksheet has several cells that
that use "conditional sum" function to total quantities of
items based defined conditions.

I'd like to have a place on "list (1)" that will provide
total quantities that span all "lists", but I'm not sure
where to start.

I think I want to count the number of worksheets
named "list (*)", then, if "list (4)" exists, add the
value of the specific cell, if not, add zero. Am I on the
right track, or is there a better way? Can someone show
how to get started? Point me in the right direction and
I'll run with it.

THANKS!
Rich

Sum of cells spanning several worksheets
 

Correction: list, not pist! (How embarassing.)

Option Explicit

Sub Main()
'puts a formula in sheet List(1), cell A1 that
'sums cells A2 for all sheets named like "list(*)".
Dim wks As Worksheet, str As String
For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) Like "list(*)" Then
Let str = str & "'" & wks.Name & "'!A2 + "
End If
Next wks
If Len(str) 0 Then
Worksheets("List(1)").Range("A1").Formula = _
"=" & Left(str, Len(str) - Len(" + "))
End If
End Sub

--
Bob Kilmer

"Rich" wrote in message
...
I have a workbook that will have a varying number of
worksheets. The worksheet names will be "list (1)"
thru "list (xx)". Each worksheet has several cells that
that use "conditional sum" function to total quantities of
items based defined conditions.

I'd like to have a place on "list (1)" that will provide
total quantities that span all "lists", but I'm not sure
where to start.

I think I want to count the number of worksheets
named "list (*)", then, if "list (4)" exists, add the
value of the specific cell, if not, add zero. Am I on the
right track, or is there a better way? Can someone show
how to get started? Point me in the right direction and
I'll run with it.

THANKS!
Rich

Sum of cells spanning several worksheets
 

Option Explicit

Sub Main()
'sums cells A2 for all sheetss named like "list(*)"
'puts the sum in sheet List(1), cell A1
Dim wks As Worksheet, total As String
total = 0
For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) Like "list(*)" Then
Let total = total + wks.Range("A2").Value
End If
Next wks
Worksheets("List(1)").Range("A1").Value = total
End Sub

--
Bob Kilmer
"Rich" wrote in message
...
I have a workbook that will have a varying number of
worksheets. The worksheet names will be "list (1)"
thru "list (xx)". Each worksheet has several cells that
that use "conditional sum" function to total quantities of
items based defined conditions.

I'd like to have a place on "list (1)" that will provide
total quantities that span all "lists", but I'm not sure
where to start.

I think I want to count the number of worksheets
named "list (*)", then, if "list (4)" exists, add the
value of the specific cell, if not, add zero. Am I on the
right track, or is there a better way? Can someone show
how to get started? Point me in the right direction and
I'll run with it.

THANKS!
Rich

Sum of cells spanning several worksheets
 

Thanks, Bob.

-----Original Message-----
Correction: list, not pist! (How embarassing.)

Option Explicit

Sub Main()
'puts a formula in sheet List(1), cell A1 that
'sums cells A2 for all sheets named like "list(*)".
Dim wks As Worksheet, str As String
For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) Like "list(*)" Then
Let str = str & "'" & wks.Name & "'!A2 + "
End If
Next wks
If Len(str) 0 Then
Worksheets("List(1)").Range("A1").Formula = _
"=" & Left(str, Len(str) - Len(" + "))
End If
End Sub

--
Bob Kilmer