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

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.


--

Regards,
Nigel




"Damien McBain" wrote in message
...
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

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" wrote in message
...
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.


--

Regards,
Nigel




"Damien McBain" wrote in message
...
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

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




"Damien McBain" wrote in message
...
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" wrote in message
...
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.


--

Regards,
Nigel




"Damien McBain" wrote in message
...
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

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

"Nigel" wrote in message
...
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




"Damien McBain" wrote in message
...
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" wrote in message
...
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.


--

Regards,
Nigel




"Damien McBain" wrote in message
...
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