Sum a range of cells from specific worksheets using VBA

[KP_SoCal]

I need to sum Range(“A10”) for all worksheets in workbook that have a Sheet Name like “Series” and cell A1 = “Applicable” in each Sheet Name like “Series”. So “SeriesXL2” with a text value of “NA” in cell A1 would be excluded from the calculation, while "SeriesJ2B" with a text value of "Applicable" would be included in the calculation. Make sense?

This is why I need to do this through VBA. It goes beyond a simple cell formula of sum(Sheet1:Sheet10!A10). Here’s a link I found describing a function that does this sort of thing, but I’m not sure how to tailor it to my needs described above. It probably doesn't help that I'm such a novice in VBA, too.

I’d be grateful for any ideas from you experts for this scenario.

 

[Norie]

Have you considered restructuring the data?

I've read most of your threads and it just seems that you are complicating things.

 

[Domenic]

Maybe something like this...

Sub test()

    Dim WS As Worksheet
    Dim MySum As Double
    
    MySum = 0
    For Each WS In ActiveWorkbook.Worksheets
        If UCase(WS.Name) Like "*SERIES*" And UCase(WS.Range("A1").Value) = "APPLICABLE" Then
            MySum = MySum + WS.Range("A10").Value
        End If
    Next WS
    
    MsgBox "Total:  " & MySum
    
End Sub

 

[KP_SoCal]

Domenic,
Great, this works!!! Now the next difficulty I'm facing with it is how to display the result in a particular cell. First off, I believe I would need to change it from being a Sub to a Function. Next, I would need to call that function within a cell to display the value. Then I would need it to update automatically if any of the values changed cells A10.

The code below seems to have some elements within it, that if properly applied will compliment your code perfectly. But I'm just not sure how to apply it. I highlighted in red what I think is important, but I'm stumped from there. Any other ideas?


Function SumAllWorksheets(InputRange As Range, InclAWS As Boolean) As Double
' adds the content of InputRange in all worksheets in the workbook
Dim ws As Worksheet, TempSum As Double
Application.Volatile True ' calculates every time a cell changes
TempSum = 0
For Each ws In ActiveWorkbook.Worksheets
If InclAWS Then ' include the active worksheet
TempSum = TempSum + _
Application.WorksheetFunction.Sum(ws.Range(InputRange.Address))
Else ' exclude the active worksheet
If ws.Name <> ActiveSheet.Name Then ' not the active worksheet
TempSum = TempSum + _
Application.WorksheetFunction.Sum(ws.Range(InputRange.Address))
End If
End If
Next ws
Set ws = Nothing
SumAllWorksheets = TempSum
End Function



Norie,
Sorry, I don't mean to complicate things. This has been a great forum. I have learned so much since my original posts. It's certainly helped in learning not to complicate things as much. However for this particular post, I've already simplified the data structure as much as possible.

 

[Domenic]

Try...

Function SumSeries(SumRange As Range) As Double

    Dim WS As Worksheet
    Dim MySum As Double
    
    Application.Volatile True
    
    MySum = 0
    For Each WS In ActiveWorkbook.Worksheets
        If UCase(WS.Name) Like "*SERIES*" And UCase(WS.Range("A1").Value) = "APPLICABLE" Then
            MySum = MySum + Application.WorksheetFunction.Sum(WS.Range(SumRange.Address))
        End If
    Next WS
    
    SumSeries = MySum
    
End Function

With the worksheet formula as follows...

=SumSeries(A10)

 

[KP_SoCal]

OUTSTANDING!!!!!!


Thanks so much!!!!

KP

 

[Domenic]

You're very welcome! Glad I could help!

 

[KP_SoCal]

Domenic, I have one more spin on this. In cell G1 for each of my several wksts whose name begins with "Series", I have the following formula entered in the cell =VLOOKUP($F$3,Main!$B$7:$C$12,2,FALSE)

If the range in my VLOOKUP ever changes, I'm stuck going through an manually readjusting the forumla in cell G1 for each of my series worksheets. However if I was able to reference the cell to a function in vba that holds my VLOOKUP formula, I would only need to change the formula in the VBA code as opposed to going to each G1 cell in my series worksheets. So instead of typing out the VLOOKUP formula in G1, I would just input the function, e.g., G1VlookUP(), in the cell instead.

I suppose the logic would flow something like this...

Function G1VlookUP()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If UCase(WS.Name) Like "*SERIES*" Cell Equals G1 Then
G1.value = VLOOKUP($F$3,Main!$B$7:$C$12,2,FALSE)
End If
Next WS
End Function


Any ideas how to put this into a code that will work?

Thanks!

 

[Domenic]

Any time you need to change the formula for each series worksheet, simply change the formula in the following code and run it...

Sub G1VlookUP()

    Dim WS As Worksheet
    
    For Each WS In ActiveWorkbook.Worksheets
    If UCase(WS.Name) Like "*SERIES*" Then
            WS.Range("G1").Formula = "=VLOOKUP($F$3,Main!$B$7:$C$12,2,FALSE)"
        End If
    Next WS

End Sub

 

[KP_SoCal]

Absolutely beautiful!!! Precisely what I was looking to do. Thanks again!!!



KP