Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Summing common cell over mutiple sheets within a file? | Excel Worksheet Functions | |||
Running mutiple excel sheets on mutiple computers | Excel Discussion (Misc queries) | |||
Summing across multiple sheets using a changeable reference cell | Excel Worksheet Functions | |||
Sumif across mutiple sheets | Excel Worksheet Functions | |||
Summing same cell/cells from multiple sheets | Excel Worksheet Functions |