swapping between worksheets in a UDF 2

[Shippwreck]

Hi,

I have a set of worksheets and i need to sum the same cell in each of them together. I was at first using THREED that i found in an excel add in on the net but then realised that when i distribute the file anyone without this add in wont be able to use it properly and seeings as the users are not always as literate as me with excel i can't really provide them with the add in and expect them to install it properly. Anyway i decided to try to write my own version from scratch. Below is how far i have got:

Function AggregateFN(celladdress)

Startsheet = Worksheets("Start").Index
Endsheet = Worksheets("End").Index
Startsheet = Startsheet + 1

Do Until Startsheet = Endsheet
Worksheets(Startsheet).Activate
Cellvalue1 = ActiveSheet.Range(celladdress).Value
Cellvalue2 = Cellvalue1 + Cellvalue2
Startsheet = Startsheet + 1
Loop

AggregateFN = Cellvalue2

End Function

The idea is that i have a workbook with lots of worksheets and i only want to sum certain sheets but that i want to be able to add more sheets if necessary and for these to be included in the sum without any extra coding. So like THREED i put in a start and end sheet either side of the sheets i wish to sum. I then have another sheet outside these that will contain the summed figures.

The idea was to make it a function with the address of the cell to be summed from each each sheet as the input. This code will run all the way through working properly if you type:

=AggregateFN("D8")

except that it gives a value of the cell in D8 in the worksheet that it is in timesd by however many sheets in between the start and end sheets which is not correct. I think the problem is with the line that activates a different sheet as it doesn't change sheets at all but i don't know whats wrong. Additionally if you omit the quotation marks however it stops at:

Cellvalue1 = ActiveSheet.Range(celladdress).Value

because it is passing the value in that cell in the current sheet.

Can someone please help me with making this work as i am now totally stuck.

Thanks in advance, this is sending me crazy!!!

Matt

http://www.orlandoluxuryvilla-pool.com

 

[GrayShades]

Perhaps a non-VBA approach might help:

I have a set of worksheets and i need to sum the same cell in each of them together.

In this case, insert a worksheet and name it: Summary. Then insert two worksheets (but don't put anything on them) and name them: First and Last. Then move all your other worksheets (except Summary) between First and Last. Then in the Summary worksheet use this formula:

=SUM(First:Last!C4)

This will sum all cells on every sheet located between the two worksheets.

Not sure if this will help, but it avoids the add-in problem and also getting everyone to enable macros.

Software: XL2002 on Win2K
Humanware: Older than dirt

 

[VBAjedi]

Working with the sheet indexes can be a bad idea, particularly for an application like this, because worksheets can be rearranged so that their visual order does not match their index order. Instead, what if you required a simple keyword to be present in the sheet name of all sheets you want to sum? Here's an little function I just coded that will sum the specified cell from all sheets that contain "Data" in their name:

Function AggregateFN(TargetCell As Range)
Dim WS As Worksheet
celladdress = TargetCell.Address
For Each WS In ActiveWorkbook.Worksheets
   If InStr(1, WS.Name, "Data") > 0 Then
      SumVal = SumVal + WS.Range(celladdress).Value
   End If
Next WS
AggregateFN = SumVal
End Function

You could also make the keyword a second argument that is passed to the function, which would allow you to use the same function to do 3D sums from different groupings of sheets. Pretty cool possibilities!

VBAjedi

 

[Shippwreck]

Thank you to both of you,

GrayShades: I wish i had known that the sum function could do that cause it would have saved me a lot of work, not just on this project but others before. Oh well i'll be using that a lot from now on.

VBAJedi: Thanks for that code, i already know of a couple of things that i'll be using it for, for this project the Sum function will suffice but as you say there are some pretty cool possibilities for that code. Plus it eliminates the need to have the sheets you need to sum in order together.

Anyway thanks again to both and both have a star!!!

http://www.orlandoluxuryvilla-pool.com

 

[GrayShades]

One other use for the First/Last worksheet approach is that you can do projection scenarios, just by moving worksheets in and out of the sequence. With two windows open (one for the Summary, the other for movement of the worksheets), you can see how that will affect the Summary sheet.

Software: XL2002 on Win2K
Humanware: Older than dirt