How to add the same cell in multiple worksheets (with VBA)?

[Damien McBain]

Hi,
I need to add the same cells in multiple worksheets. I want the worksheets
to be selected dynamically using a range on another worksheet which contains
the names of the worksheets to sum. I don't want any formulas in the summary
worksheet - I want to populate the cells with values.

I'm thinking something like this to populate a Collection (I've never used
collections before):
--------------------
Dim Allsheets As New Collection
For Each branch In Worksheets("Tables").Range("BusAreaList")
Allsheets.Add Item:=branch
Next branch
--------------------
....but I don't know how to use the Items in the Collection to specify which
cells to sum (or even if I can!). Am I on the right track?
Any help appreciated.
TIA
Damien

 

[Nigel]

No need to use collection, you say you already have a list of sheets in the
range BusAreaList so use that.

Something like

Dim c as Range, myTotal as double
myTotal = 0
For each c in Range("BusAreaList")
myTotal = myTotal + Sheets(c.value).Range("A1")
Next c

The above adds the value in A1 on all sheets named in the range BusAreaList
to the variable myTotal. You might want to validate that the list sheet
names are valid.

 

[Damien McBain]

Thanks for that Nigel

I was hoping not to have to do it like that because there are a couple of
hundred cells I need to resolve in a similar manner. It's going to make the
code very long and laborious (but maybe necessary).

I thought I might be able to use the same principal as the worksheet
function:
=Sum(Sheet2:Sheet20!B8)
.... but I don't know how to specify a multi sheet range like that in VBA
(given that the sheet names in my workbook are dynamic - but always listed
in the range "BusAreaList")

cheers,

Damien

 

[Nigel]

You can use the application worksheet function in VBA to replicate the
=Sum(Sheet2:Sheet20!B8) to get the sum from multiple sheets, but as you say
the sheets list is not contiguous nor is it fixed. Even if you could use
this approach you would still need a code line for each summation.

The loop I proposed could be run just once and each summation could be
completed in that, but you probably know that already, but are just trying
to avoid the coding!

I cannot think of another option at the moment.....

--

Regards,
Nigel
(e-mail address removed)

 

[Damien McBain]

The loop I proposed could be run just once and each summation could be

completed in that, but you probably know that already, but are just trying
to avoid the coding!

You got that right!

Thanks for taking the time to help.

cheers

Damien