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 Removed)
"Damien McBain" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <nigel-(E-Mail Removed)> wrote in message
> news:728F418D-0410-40D4-BD05-(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Damien McBain" <(E-Mail Removed)> wrote in message
>> news:O67%(E-Mail Removed)...
>>> 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
>>>
>>
>
>
Similar Threads
