# Sum a range of cells from specific worksheets using VBA

#### KP_SoCal

##### Board Regular
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
Have you considered restructuring the data?

#### Domenic

##### MrExcel MVP
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
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 + _
Else ' exclude the active worksheet
If ws.Name <> ActiveSheet.Name Then ' not the active worksheet
TempSum = TempSum + _
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

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
End If
Next WS

SumSeries = MySum

End Function``````

With the worksheet formula as follows...

=SumSeries(A10)

#### KP_SoCal

##### Board Regular
OUTSTANDING!!!!!!

Thanks so much!!!!

KP

#### Domenic

##### MrExcel MVP

You're very welcome! Glad I could help!

#### KP_SoCal

##### Board Regular
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

##### MrExcel MVP
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
Absolutely beautiful!!! Precisely what I was looking to do. Thanks again!!!

KP

Replies
3
Views
88
Replies
1
Views
51
Replies
3
Views
193
Replies
11
Views
139
Replies
3
Views
720

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.

### Which adblocker are you using?

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

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