Summing same cell in mutiple Sheets

[Guest]

Excel 2002-2003. I build a workbook programmatically. The workbook has
mutiple sheets (and I know how many programmatically). I add a last sheet
that I want to be a "wrap" of all the other sheets. How do I
programmatically set a cell in the last sheet to be the sum of the same cell
in all of the other sheets? Also, is there a way to do this programmatically
for a range of cells rather than cell by cell? Thanks for the help. God
bless.

 

[KL]

Hi,

Try the below code

Regards,
KL

Sub test()
With ThisWorkbook
LastSheet = .Sheets.Count - 1
For Each c In .Sheets("Wrap").Range("A1:A10")
MyFormula = "=SUM('" & Sheets(1).Name & ":" & _
Sheets(LastSheet).Name & "'!" & c.Address & ")"
c.Value = Evaluate(MyFormula)
Next
End With
End Sub

 

[Bob Phillips]

Doug,

Here is an alternative that puts a formula in cell A1 on the Wrap sheet.
This all accumulates into 1 cell, as against KL's accumulating into a mapped
range, and by using a formula, any future changes are reflected

Worksheets("Wrap").Range("A1").Formula = _
"=SUM('" & Worksheets(1).Name & ":" & _
Worksheets(Worksheets.Count - 1).Name & "'!A1:A10)"


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[KL]

Opps! Thanks Bob, you are probably right - entering a formula is a more
natural solution rather than just a value.

So, Doug, if you still want the cell-by-cell sum then you can just change
the following line in my code:

c.Value = Evaluate(MyFormula)

to:

c.Formula = MyFormula

Regards,
KL

 

[Guest]

I did just that and all works well. God bless you both!

 

[Tom Ogilvy]

You may want to look in help at 3D ranges (which are what Bob and KL
actually use). If you put in two dummy sheets (keep the sheets blank)
Start
Last
as example, and set up your summary sheet after Last, then in the summary
sheet you can put (example cell B2)

=Start!Last!B2

then select this cell and drag fill down and across.

Now any sheets ented (in the tab order) between Start and End will be
included in the sum. this should be much more efficient and automatic than
running code each time the sheets change. Also, you can drag sheets out of
and between these two sheets if you want to do some "what if" type analysis
as an example.

 

[KL]

Tom,

Your example doesn't seem to work for me (XL2000)

Regards,
KL

 

[Tom Ogilvy]

Tested in xl97, xl2000, xl2002

works fine for me. Expect it to work in every version of excel that
supports 3D ranges. Perhaps my instructions have not been sufficient for
you to implement although the concept is quite simple.

Start sheet1 sheet2 sheet3 Last Summary

would represent the tab order

in summary, C3

=Start:End!C3

drag fill down and across. Look at D4 in summary. It should appear as

=Start:End!D4

on Summary, C3 should reflect the sum of Sheet1!C3, Sheet2!C3, Sheet3!C3.

Drag a Sheet4 before Last and the sum in Summary!C3 will include
sum of Sheet1!C3, Sheet2!C3, Sheet3!C3,Sheet4!C3

Not sure how to make it clearer.

 

[KL]

Not sure how to make it clearer.

I guess by writing

=SUM(Start:End!C3)

not

=Start:End!C3 (which returns #REF!)

as in my ignorance I started to think you'd discovered a new way of summing
numbers up.

Kindly,
KL

 

[Tom Ogilvy]

My bad, on the typo.

I did start out:

You may want to look in help at 3D ranges (which are what Bob and KL

actually use).

so I wasn't introducing anything new nor claiming that. Hopefully the OP
was not lead equally astray although it appears that he is happily
implementing custom code that will be less flexible, require more
maintenance and limit usability.