Sheets that mirror each other....

[Robert Crandal]

I have two different spreadsheets (Spreadsheet's 1 and 2)
that I want to mirror each other. Here is the code that I am
using to achieve this for each sheet:

'
' Worksheet_change() for "Sheet1"
'
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
For Each t In Target
Sheet2.Range(t.Address(0, 0)).Value = t.Value
Next t
Application.EnableEvents = True
End Sub

'
' Worksheet_change() for "Sheet2"
'
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
For Each t In Target
Sheet1.Range(t.Address(0, 0)).Value = t.Value
Next t
Application.EnableEvents = True
End Sub


Does anybody see any flaws in the code above??? I want both sheets
to miror each other exactly at all times, but I noticed in a few situations
that the data on both sheets does not match exactly and I'm puzzled
as to why??

Would anybody mind running the above code and try to find situations
that might break the mirror?? Otherwise, can anybody see a flaw in
the code?

thank you

 

[Robert Crandal]

Okay....I found a scenario which will break my code below.

On Sheet1, type some data into any cell, but do NOT press
the Enter key to finalize the data entry. After you type in
the data, immediately change to Sheet2. Now change back
to Sheet1. Notice that the data on both sheets is different
now??

Does anybody know how I can solve this problem scenario???

Basically, if I change to Sheet2 before I actually trigger a
"change" event in Sheet1, the data will not transfer over to Sheet2.

 

[john]

Robert,
you posted this question before & had some responses including one I posted.
Code I posted as an idea is as follows - it may or may not do what you are
trying to do but may give you some further ideas.

I am out of office now until new year!

'sheet 1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range

Application.EnableEvents = False

Sheets(2).Cells.ClearContents

Set rng1 = Sheets(1).UsedRange

Set rng2 = Sheets(2).Range("A1")

rng1.Copy rng2

Application.EnableEvents = True

End Sub

'sheet 2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range

Application.EnableEvents = False

Sheets(1).Cells.ClearContents

Set rng1 = Sheets(2).UsedRange

Set rng2 = Sheets(1).Range("A1")

rng1.Copy rng2

Application.EnableEvents = True

End Sub