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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Damien McBain - 24 Jan 2008 00:46 GMT

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

Reply to this Message

Nigel - 24 Jan 2008 04:04 GMT

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.

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

> Hi,
> I need to add the same cells in multiple worksheets. I want the worksheets
[quoted text clipped - 15 lines]
> TIA
> Damien

Reply to this Message

Damien McBain - 24 Jan 2008 06:58 GMT

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

> No need to use collection, you say you already have a list of sheets in
> the range BusAreaList so use that.
[quoted text clipped - 31 lines]
>> TIA
>> Damien

Reply to this Message

Nigel - 24 Jan 2008 12:22 GMT

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.....

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

> Thanks for that Nigel
>
[quoted text clipped - 48 lines]
>>> TIA
>>> Damien

Reply to this Message

Damien McBain - 24 Jan 2008 23:57 GMT

> 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

> 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
[quoted text clipped - 59 lines]
>>>> TIA
>>>> Damien

Reply to this Message