Need to sum (or find average) of a cell range on a variable number of worksheets

[hlwilliams]

1st post ever so be kind please.

I created a macro that asks the user how many test forms they need of one type of test, then how many they need of another and so forth. When complete the macro spits out a workbook with X number of worksheet "Cats 01","Cats 02" and so forth, and then Y number of "Dogs 01',"Dogs 02" and so forth. So far that chunk works like a champ. Now I need a summary sheet of all the data across the workbook, regardless of the # of worksheets there are which is what is confusing me.

For example I want to sum cell A3 through the X number of the "Cat XX" worksheets, but since the number of worksheets is variable, I am not sure how to express what I need in a formula or vba. Then I would need to average cell B2 throughout the Y number of "Dogs XX" sheets, again being a variable number.

I was trying to figure out how to used a named range as an array, then looking for left(workbookname,3) = cat or what have you to sum/average the cells across the sheets, but I am still running in circles.
I was able to create a named range "SheetNames" and then created a tab that lists all of the possible names in the workbook to start with, and now I am lost.


I am a excel/vba noob so please be kind and I apologize if I am unclear. I've seen sumproduct(sumif(indirect....) but these formulas are a little greek to me still.

Thanks in advance!!

 

[JoeMo]

Maybe something like this:
Dim catSum as double, catNum as double, sh as worksheet, catAvg as double
for each sh in Activeworkbook.sheets
if sh.name like "*Cat*" then
catSum=catSum+sh.range("A3").Value
catNum = catNum+1
End if
Next sh
catAvg=catSum/catNum
'rest of code

This is bare bones, obviously doesn't include any error handling. You could repeat this block for Dog sheets, etc.

 

[hlwilliams]

Maybe something like this:
Dim catSum as double, catNum as double, sh as worksheet, catAvg as double
for each sh in Activeworkbook.sheets
if sh.name like "*Cat*" then
catSum=catSum+sh.range("A3").Value
catNum = catNum+1
End if
Next sh
catAvg=catSum/catNum
'rest of code

This is bare bones, obviously doesn't include any error handling. You could repeat this block for Dog sheets, etc.

Thank you for your response. I can see that the macro is working, however how to I get the total to show up in a cell? I get that it is adding/averaging behind the scenes, but I want that number to spit out on a summary sheet. I am REALLY new to this. Sorry if I am asking stupid questions!

***DISREGARD ABOVE***

Worksheets("summary sheet").Activate
Range("XXX").Value = CatSum

This works for me - thank you!!!

 

[JoeMo]

Thank you for your response. I can see that the macro is working, however how to I get the total to show up in a cell? I get that it is adding/averaging behind the scenes, but I want that number to spit out on a summary sheet. I am REALLY new to this. Sorry if I am asking stupid questions!

***DISREGARD ABOVE***

Worksheets("summary sheet").Activate
Range("XXX").Value = CatSum

This works for me - thank you!!!

You are welcome -thanks for your reply, and good work on figuring out how to put the sum where you want it.