Solved: Sum across worksheets VBA

[Shazam]

Hi Everyone,


I have this formula below that will sum across worksheets, in my example workbook below.

=SUM('06-01-2007:06-06-2007'!C:C)


But I would like to convert it into a vba code to get the First & Last date of the worksheets in my workbook BUT ignore “Performance Data”, “Individual Report” & “Comparison Report”.

The dates of the worksheets will vary time to time in my workbook. So if the code could get the First and Last date and apply into the formula that will be great.

[p45cal]

Since your sheet names can be recognised by Excel as dates I used the IsDate function:

Sub blah()
For Each sht In ActiveWorkbook.Sheets
If IsDate(sht.Name) Then mytotal = mytotal + Application.WorksheetFunction.Sum(sht.Columns("C"))
Next sht
MsgBox "Total = " & mytotal
End Sub

[Shazam]

Since your sheet names can be recognised by Excel as dates I used the IsDate function:

Sub blah()
For Each sht In ActiveWorkbook.Sheets
If IsDate(sht.Name) Then mytotal = mytotal + Application.WorksheetFunction.Sum(sht.Columns("C"))
Next sht
MsgBox "Total = " & mytotal
End Sub

Thank you very much for the code. Actually I sent the wrong sample book. The right sample has the correct worksheets names. The name of the worksheets contains Texts. So when I ran you your code I get a blank result. Anyway to get around that?

Also can we input the results in cell B2?

[Bob Phillips]

[vba]

Sub blah()
Dim sht As Worksheet
Dim myTotal As Double

For Each sht In ActiveWorkbook.Sheets
If IsDate(Replace(ActiveSheet.Name, "Test ", "")) Then
myTotal = myTotal + Application.Sum(sht.Columns("C"))
End If
Next sht
Range("B2").Value = myTotal
End Sub


[/vba]

[Shazam]

[vba]

Sub blah()
Dim sht As Worksheet
Dim myTotal As Double

For Each sht In ActiveWorkbook.Sheets
If IsDate(Replace(ActiveSheet.Name, "Test ", "")) Then
myTotal = myTotal + Application.Sum(sht.Columns("C"))
End If
Next sht
Range("B2").Value = myTotal
End Sub
[/vba]

Hi xld Thank you for replying. I ran your code on worksheet "Performance Data" but I get a zero result. I'm sorry I should have mention that I would like to run the code on "Performance Data" and have the result in cell B2 in a formula.

Something like this,

[vba]
Range("B2").Formula = myTotal
[/vba]

[Bob Phillips]

Sorry Shaz, typo

[vba]

Sub blah()
Dim sht As Worksheet
Dim myTotal As Double

For Each sht In ActiveWorkbook.Sheets
If IsDate(Replace(sht.Name, "Test ", "")) Then
myTotal = myTotal + Application.Sum(sht.Columns("C"))
End If
Next sht
Range("B2").Value = myTotal
End Sub
[/vba]

[Shazam]

Thank You so much xld.

One more thing instead of the results comming as a vaule can it be in a formula result instead?

I tried to modify this line,

Range("B2").Value = myTotal

into this

Range("B2").Formula = myTotal


But no avail.

[Bob Phillips]

If it could be a formula, then you would add a formula in Excel would you not?

is it just because you don't knoW the names of the first and last workbooks when you run it?

[p45cal]

something like this at the end?:

    mystr = "=""the total is "" & " & myTotal
    Range("B2").Formula = mystr

[Bob Phillips]

LOL!

[Shazam]

If it could be a formula, then you would add a formula in Excel would you not?

Yes I could But this is a daily routine. The code you provided will be a Sub Workbook_Open event. So when ever a user open the workbook the macro will trigger.

is it just because you don't knoW the names of the first and last workbooks when you run it?

Yes I don't know the first and last names of the worksheets.


Also the reason why I would like a formula result shown is because when the supervisor open the workbook the formula will tell him or her how the values were calculated.

I left a expected result in cell D2 in the workbook below.

Is possible to have it in a formula?

[Bob Phillips]

Of course, but it does rely on the worksheets being in order, the non-SUM ones that is

[vba]

Private Sub Workbook_Open()

Dim sht As Worksheet
Dim myTotal As Double
Dim sFirst As String
Dim sLast As String
Dim i As Long

For i = 1 To ThisWorkbook.Worksheets.Count
Set sht = ThisWorkbook.Worksheets(i)
If IsDate(Replace(sht.Name, "Test ", "")) Then
If sFirst = "" Then sFirst = sht.Name
sLast = sht.Name
End If
Next i
Range("B2").Formula = "=SUM('" & sFirst & ":" & sLast & "'!C:C)"
End Sub
[/vba]

[Shazam]

Of course, but it does rely on the worksheets being in order, the non-SUM ones that is

Yes the worksheets will always be in order.

[vba]

Private Sub Workbook_Open()

Dim sht As Worksheet
Dim myTotal As Double
Dim sFirst As String
Dim sLast As String
Dim i As Long

For i = 1 To ThisWorkbook.Worksheets.Count
Set sht = ThisWorkbook.Worksheets(i)
If IsDate(Replace(sht.Name, "Test ", "")) Then
If sFirst = "" Then sFirst = sht.Name
sLast = sht.Name
End If
Next i
Range("B2").Formula = "=SUM('" & sFirst & ":" & sLast & "'!C:C)"
End Sub
[/vba]

Perfect thank you very much. Now with your solution I could apply with other formulas that I use with 3D reference. This will save me allot of caluclation time. Once again thank You very much!!

[Carl A]

I not sure if this is what you need in the way of a formula.
=SUM('*'!C:C) this will sum all the sheets in the range C:C except the sheet the formula resides on. So used as a summary sheet calculation you could use VBA to put the formula in the cell you will use for summing.

[vba]Range("D2").Formula = "=SUM('*'!C:C)"[/vba]

[Shazam]

I not sure if this is what you need in the way of a formula.
=SUM('*'!C:C) this will sum all the sheets in the range C:C except the sheet the formula resides on. So used as a summary sheet calculation you could use VBA to put the formula in the cell you will use for summing.

[vba]Range("D2").Formula = "=SUM('*'!C:C)"[/vba]

Thanks Carl A,

Neat trick!!