How to summarize two sheets in the third sheet
 

Dear All,

I have three sheets (sheet1, sheet2, sheet3)

I want to write macro which will do following:
I will select some cells in the "sheet1" and in each cell of the selection
write sum of the same coordinate cells from sheet2 and sheet3.

Thank you for your help.
Lado

How to summarize two sheets in the third sheet
 

Thank you Ron!!!

It's very helpful.

Respectfully,
Lado

"Ron de Bruin" wrote in message
...
Try this

Sub test()
Dim cell As Range
For Each cell In Selection
cell.Value = Sheets("Sheet2").Range(cell.Address) +
Sheets("Sheet3").Range(cell.Address)
Next cell
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"L.K." wrote in message
...
Dear All,

I have three sheets (sheet1, sheet2, sheet3)

I want to write macro which will do following:
I will select some cells in the "sheet1" and in each cell of the
selection write sum of the same coordinate cells from sheet2 and sheet3.

Thank you for your help.
Lado

How to summarize two sheets in the third sheet
 

You could use your macro recorder, the code will look something like
this

Sub Macro1()

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[6]:R[14]C[6])"
Sheets("Sheet1").Select
Range("A3").Select
End Sub


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=539626

How to summarize two sheets in the third sheet
 

Try this

Sub test()
Dim cell As Range
For Each cell In Selection
cell.Value = Sheets("Sheet2").Range(cell.Address) + Sheets("Sheet3").Range(cell.Address)
Next cell
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"L.K." wrote in message ...
Dear All,

I have three sheets (sheet1, sheet2, sheet3)

I want to write macro which will do following:
I will select some cells in the "sheet1" and in each cell of the selection write sum of the same coordinate cells from sheet2 and
sheet3.

Thank you for your help.
Lado

How to summarize two sheets in the third sheet
 

Similar to Ron's code here is one that will fire automatically each time
you change cells in sheet1. You would put the code in the code module for
sheet1, not a general code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
With Selection
If .Cells.Count = 1 Then
.Value = Worksheets("Sheet2").Range(Target.Address).Value + _
Worksheets("Sheet3").Range(Target.Address).Value
Else
For Each c In Target
c.Value = Worksheets("Sheet2").Range(c.Address).Value + _
Worksheets("Sheet3").Range(c.Address).Value
Next c
End If
End With
End Sub

P.S. if you need to constrain this code to work only in a certain range on
the worksheet, post back.

Mike F
"L.K." wrote in message
...
Dear All,

I have three sheets (sheet1, sheet2, sheet3)

I want to write macro which will do following:
I will select some cells in the "sheet1" and in each cell of the selection
write sum of the same coordinate cells from sheet2 and sheet3.

Thank you for your help.
Lado

How to summarize two sheets in the third sheet
 

Just in case you want to constrain this to a certain selection here is some
code that will work in Range("A1:B5"). You can change this in the code (2
places) to meet your needs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range

If Not Application.Intersect(Range("A1:B5"), Target) Is Nothing Then
With Selection
If .Cells.Count = 1 Then
.Value = Worksheets("Sheet2").Range(Target.Address).Value + _
Worksheets("Sheet3").Range(Target.Address).Value
Else
For Each c In Target
If Not Application.Intersect(Range("A1:B5"), c) Is Nothing Then
c.Value = Worksheets("Sheet2").Range(c.Address).Value + _
Worksheets("Sheet3").Range(c.Address).Value
Else
c.Value = c.Value
End If
Next c
End If
End With
End If
End Sub

P.S. if you select an area where cells are in and outside the range A1:B5,
it will sum only the cells inside the range and all other cells will remain
unchanged. (Else c.Value = c.Value)

Mike F

"L.K." wrote in message
...
Dear All,

I have three sheets (sheet1, sheet2, sheet3)

I want to write macro which will do following:
I will select some cells in the "sheet1" and in each cell of the selection
write sum of the same coordinate cells from sheet2 and sheet3.

Thank you for your help.
Lado

How to summarize two sheets in the third sheet
 

Thank You!
Great.
Lado

"Mike Fogleman" wrote in message
...
Just in case you want to constrain this to a certain selection here is
some code that will work in Range("A1:B5"). You can change this in the
code (2 places) to meet your needs.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range

If Not Application.Intersect(Range("A1:B5"), Target) Is Nothing Then
With Selection
If .Cells.Count = 1 Then
.Value = Worksheets("Sheet2").Range(Target.Address).Value + _
Worksheets("Sheet3").Range(Target.Address).Value
Else
For Each c In Target
If Not Application.Intersect(Range("A1:B5"), c) Is Nothing Then
c.Value = Worksheets("Sheet2").Range(c.Address).Value + _
Worksheets("Sheet3").Range(c.Address).Value
Else
c.Value = c.Value
End If
Next c
End If
End With
End If
End Sub

P.S. if you select an area where cells are in and outside the range A1:B5,
it will sum only the cells inside the range and all other cells will
remain unchanged. (Else c.Value = c.Value)

Mike F

"L.K." wrote in message
...
Dear All,

I have three sheets (sheet1, sheet2, sheet3)

I want to write macro which will do following:
I will select some cells in the "sheet1" and in each cell of the
selection write sum of the same coordinate cells from sheet2 and sheet3.

Thank you for your help.
Lado