Sum a range of cells from specific worksheets using VBA

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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. :)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Have you considered restructuring the data?

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Maybe something like this...

Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
116
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697

ADVERTISEMENT

Try...

Code:
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)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697

ADVERTISEMENT

You're very welcome! Glad I could help!
 

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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? :rolleyes:

Thanks! ;)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Any time you need to change the formula for each series worksheet, simply change the formula in the following code and run it...

Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
116
Absolutely beautiful!!! Precisely what I was looking to do. Thanks again!!!

:biggrin:

KP
 

Forum statistics

Threads
1,136,845
Messages
5,678,091
Members
419,741
Latest member
BKN336

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top