Summing same cell in mutiple Sheets
 

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.

Summing same cell in mutiple Sheets
 

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


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

Summing same cell in mutiple Sheets
 

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


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

Summing same cell in mutiple Sheets
 

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

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


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

Summing same cell in mutiple Sheets
 

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

"KL" wrote:

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

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


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

Summing same cell in mutiple Sheets
 

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.

--
Regards,
Tom Ogilvy

"Chaplain Doug" wrote in message
...
I did just that and all works well. God bless you both!

"KL" wrote:

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

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


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

Summing same cell in mutiple Sheets
 

Tom,

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

Regards,
KL

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

--
Regards,
Tom Ogilvy

"Chaplain Doug" wrote in message
...
I did just that and all works well. God bless you both!

"KL" wrote:

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

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


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

Summing same cell in mutiple Sheets
 

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.


--
Regards,
Tom Ogilvy

Summing same cell in mutiple Sheets
 

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