3D sum using VBA

[Gareth]

I have a large number of cells on sheet1 (approx 1,300)
which need 3D sums from other sheets in the same book. I
have decided to try and do this using code but have been
unable to get it to work.
Another complication is that a new sheet is added to the
file every week.
What would be the code for the following and how could it
be amended to take into account of the new sheet each week?

=SUM('WE 8 4 05:WE 1 4 05'!R4)

Thanks in advance.

Gareth

 

[OJ]

Gareth,
If you put this in a Module you can use it like a normal worksheet
function. You call it like this

=SumAcrossSheets(A1,"WE*")

and it will return #Value! if one (or more) of the values is not
numeric...

Public Function SumAcrossSheets(rngCellRef As Range, strID As String)
As Long
Dim intInc As Integer
Application.Volatile
For intInc = 1 To ThisWorkbook.Worksheets.Count
With ThisWorkbook.Worksheets(intInc)
If .Name Like strID Then
Let SumAcrossSheets = SumAcrossSheets +
..Range(rngCellRef.Address)
End If
End With

Next intInc
End Function

Hth,
OJ

 

[Gareth]

Thanks for this but I was hoping to do something like:

Sub totals()
Dim cell As Range
For Each cell in Range("B4:B30")
cell.value = 'whatever the code would be?
Next cell
End Sub

Is this possible?

Gareth

 

[Tom Ogilvy]

put in two blank sheets, one named First and one named Last

put all the sheets you want to sum between these two sheets.

Sub totals()
Dim cell As Range
For Each cell in Range("B4:B30")
cell.Formula = "=Sum('First:Last'!" & cell.Address & ")"
Next cell
End Sub


If that absolutely isn't acceptable

Sub totals()
Dim cell As Range, bFirst as Boolean
Dim sh as Worksheet
Dim sFirst as String, sLast as String
bFirst = True
for each sh in Thisworkbook.Worksheets
if lcase(left(sh.name,2)) = "we" then
if bFirst then
sFirst = sh.name
bFirst = False
end if
sLast = sh.name
end if
Next
For Each cell in Range("B4:B30")
cell.Formula = "=Sum('" & sFirst & ":" & sLast & "'!" & cell.Address & ")"
Next cell
End Sub