sending same data to multiple noncorresponding cells

R

rclark

I am a new user of Excel, so please forgive me if this is a dumb question....
How can I send data entered into one cell to noncorresponding cells on other
sheets within the same workbook? And, if one of the "target" cells on the
other sheets already contains data, how can I keep the "transferred" data
from replacing it, but rather moving to a different specified cell?
 
M

Myrna Larson

It could be done with a macro, but for a "new user of Excel" that may not be
an option.
 
E

Erin Searfoss

The second scenario can be done with an event procedure. Right click on the
sheet tab where the input cell is located and select view code. Paste this
code and edit as applicable for your situation.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

Dim NewCell As Range

If Target <> Range("A1") Then Exit Sub

Application.EnableEvents = False

Set NewCell = Sheets("Sheet2").Range("A1")

Do Until IsEmpty(NewCell)
Set NewCell = NewCell.Offset(0, 1)
Loop

NewCell.Value = Target.Value

Application.EnableEvents = True

End Sub

In this example the cell being changed is A1 of Sheet1 and the results are
being sent to row 1 of Sheet2. The macro starts in A1 of Sheet2 and looks
for the next empty cell to the right. This particular code will only work if
cell A1 on Sheet1 is being directly changed. There would be more involved if
A1 contained a formula and you wanted to record each change in calculation.


For more info see Chip Pearson's article on Event Procedures.
http://www.cpearson.com/excel/events.htm
 
E

Erin Searfoss

You'll have to create one macro to handle all cases. Something like this
should work. Highlight all the cells you want to trigger this event. In the
Name Box (the drop-down list at the left side of the formula bar) type
EntryRange to name this combination of cells. (If you choose a lot of cells
they won't all be included in this range. If this is the case don't bother
naming the range and delete the line

If Intersect(Target, Range("EntryRange")) Is Nothing Then Exit Sub

The Case Else statement will take care of it. It will just slow things down
a bit when someone changes a non-trigger cell.)

In the code below Range1, Range2, and Range3 refers to the name or cell
reference of each of the trigger cells. If you have more cells just add more
Case statement before the Case Else.

Note: the Select Case statement is a fancy multiple If..Then...Else
statement.

Good luck.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

Dim NewCell As Range

If Intersect(Target, Range("EntryRange")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Select Case Target.Address

Case Range("Range1").Address
Set NewCell = Sheets("Sheet2").Range("A1")
Case Range("Range2").Address
Set NewCell = Sheets("Sheet2").Range("A2")
Case Range("Range3").Address
Set NewCell = Sheets("Sheet2").Range("A3")
Case Else
Application.EnableEvents = True
Exit Sub

End Select

Do Until IsEmpty(NewCell)
Set NewCell = NewCell.Offset(0, 1)
Loop

NewCell.Value = Target.Value

Application.EnableEvents = True

End Sub
 
Top