sheets that mirror each other

[Robert Crandal]

My workbook only has 2 sheets. If the user edits cell "A1"
on Sheet1, I want that data to be copied into "A1" on Sheet2.
My users also work on Sheet2, so if they edit cell "A1" on
Sheet2, I need to copy that same data to the "A1" on Sheet1.

How can I do this?

 

[trip_to_tokyo]

Hi Robert.

I don't think that you can do that.

You can do the following:-

1. Enter the data in sheet 1 cell A1.

2. On sheet 2 at cell A1 enter the following:-

=Sheet1!A1

3. This way the data in both cells will be identical.

4. I do not think that you can do the, “vice / versa†though.

If my comments have helped please hit Yes.

Thanks!

 

[Mike Fogleman]

In sheet1 code module put this:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet2.Range(Target.Address).Value = Target.Value
End Sub

in Sheet2 code module this:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range(Target.Address).Value = Target.Value
End Sub

Mike F

 

[Robert Crandal]

Wouldn't this cause an infinite loop or something???
Sheet2 will try to mirror the contents of Sheet 1, but
Sheet 1 will also notice the cell change and try to mirror Sheet2
and vice versa, on and on and on???

What do you think?

 

[Mike Fogleman]

It worked OK on my test sheets. This assigns a value to a cell and does not
trigger the Change event in the other sheet. Editing the cell does, but only
for that sheet.

Mike F

 

[Robert Crandal]

Try putting a MsgBox("Im in Sheet1") statement in the
Worksheet_Change() function for sheet1. It will
give you about 45 message boxes/dialog boxes that
say "I'm in Sheet1". It appears that Excel runs out of
stack space at that point.

so it appears that the Change event is triggered in the
other sheets as well.

 

[Mike Fogleman]

Let me study this once more. There must be a way, maybe with an IF
statement.

Mike F

 

[Robert Crandal]

How about the following code in the Worksheet_Change()
for Sheet1:

If Application.ActiveSheet.CodeName = "Sheet1" Then
' copy data to Sheet2
End if

Then, in the code for Worksheet_Change() for Sheet2,
use the following:

If Application.ActiveSheet.CodeName = "Sheet2" Then
' copy data to Sheet1
End if

What do you think about that idea??

 

[Mike Fogleman]

Yes, that seems to do it:
sheet1 code

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.ActiveSheet.CodeName = "Sheet1" Then
Sheet2.Range(Target.Address).Value = Target.Value
Else
Exit Sub
End If
'MsgBox ("Im in Sheet1")
End Sub

sheet2 code

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.ActiveSheet.CodeName = "Sheet2" Then
Sheet1.Range(Target.Address).Value = Target.Value
Else
Exit Sub
End If
'MsgBox ("Im in Sheet2")
End Sub

Mike F