PDA

View Full Version : [SOLVED] Accumulating sheet with a range of cells in the other sheet with a range of cells



k0st4din
10-27-2013, 10:15 AM
Hello friends, I searched the forum but could not find what I need. There were two areas in which is stored information of a single cell in the same cell or range of cells.
But I need the following:
This is an example, I will change the sheets, then:
from sheet1 with cells "E45:F55", to my accumulated information in sheet2 in cells (I2:J12).
Thank you in advance for helping.

Aussiebear
10-27-2013, 04:23 PM
Just to clear this issue up are you,
1. Wanting to copy from Sheet 1 cells E45:F55 an paste to sheet 2 cells I2:J12, and
2. How does this event get triggered?

Paul_Hossler
10-27-2013, 07:18 PM
from sheet1 with cells "E45:F55", to my accumulated information in sheet2 in cells (I2:J12).


This must be a dumb question, but why would a formula in Sheet2, Cell I2 not do that?

Is there a need to actually put a value there instead of a formula?

(OK, that was 2 questions)



=Sheet1!E45



Repeating to the other destination cells


Paul

k0st4din
10-27-2013, 11:00 PM
Hello, I know how to do it with a formula but notice! - I want accumulation in these cells.
Accumulation will be activated by pressing a button.

I will give an example of just one cell (which is the same for the entire range of cells)
if sheet1, E45=2 in sheet2, I2=2
next sheet1, E45=20, press button and in sheet2, I2=22 (also be the same for all other cells).
Here's what I found on the internet, but it is not what I want:

Private Sub Worksheet_Change
Dim vOld As Variant
Dim vNew As Variant

With Target
If .Count = 1 And Not Intersect(.Cells, Range("A1:A10")) Is Nothing Then
vNew = .Value
If IsNumeric(vNew) Then
With Application
.EnableEvents = False
.Undo
vOld = Target.Value
If IsNumeric(vOld) Then
Target.Value = vOld + vNew
Else
Target.Value = vNew
End If
Select Case .MoveAfterReturnDirection
Case xlToRight: Target.Offset(0, 1).Select
Case xlDown: Target.Offset(1, 0).Select
Case xlToLeft: Target.Offset(0, -1).Select
Case xlUp: Target.Offset(-1, 0).Select
End Select
.EnableEvents = True
End With
End If
End If
End With

Private Sub Worksheet_Change
Static dAccumulator As Double
With Target
If .Address(False, False) = "A1" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dAccumulator = dAccumulator + .Value
Else
dAccumulator = 0
End If
Application.EnableEvents = False
.Value = dAccumulator
Application.EnableEvents = True
End If
End With
End Sub

k0st4din
10-28-2013, 10:31 PM
Friends anyone have any idea?
Ie I want to say that if the formula will be very easy, but in the first sheet, the range of cells to change the numbers of certain sizes and before I print them on paper I should build these values ​​into the second sheet .

camillobenso
10-28-2013, 11:11 PM
Sorry,i have no idea about this but i try to get information about this.

k0st4din
10-29-2013, 12:11 AM
I believe that can happen. Once people have done to accumulate, in one place, I probably need to change something to make it accumulates in other Sheet2. At least we have a start and may otherwise be able to do.

k0st4din
10-30-2013, 01:49 AM
Here is a file:

p45cal
10-30-2013, 01:11 PM
Sub blah()
Set sce = Sheets("Sheet1").Range("E45")
Set Destn = Sheets("Sheet2").Range("I2")
For r = 0 To 10
For c = 0 To 1
Destn.Offset(r, c).Value = Destn.Offset(r, c).Value + sce.Offset(r, c).Value
Next c
Next r
End Sub

or:
Sub blah2()
Sheets("Sheet2").Range("I2:J12") = [(Sheet1!E45:F55)+(Sheet2!I2:J12)]
End Sub

k0st4din
10-30-2013, 10:39 PM
No, no, this is not true, only one line and solve all my headaches. I turned the whole internet "codes without end" and how simple it was.
p45cal you're an amazing guy.
Thanks a thousand.
Bow to you, you're just great.