Mirror data in two sheets
 

I want the data in Sheet1 and Sheet2 to mirror
each other, but only in the range of A1:C10.

So, if someone types "apple" on Sheet1 in cell A1,
I want "apple" to automatically populate cell A1
in Sheet2 (or vice versa).

How can this be achieved? (I hope this doesn't
create an infinite loop situation where data constantly
gets copied back and forth between both sheets)

Mirror data in two sheets
 

Hi Robert,

Am Sat, 29 Nov 2014 01:40:57 -0700 schrieb Robert Crandal:

I want the data in Sheet1 and Sheet2 to mirror
each other, but only in the range of A1:C10.

try following code in code module of ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Intersect(Target, Sh.Range("A1:C10")) Is Nothing Then Exit Sub

Dim rngDest As Range
Dim Addr As String

Application.EnableEvents = False

Addr = Target.Address
Select Case Sh.Name
Case "Sheet1"
Set rngDest = Sheets("Sheet2").Range(Addr)
Case "Sheet2"
Set rngDest = Sheets("Sheet1").Range(Addr)
End Select
rngDest = Target.Value

Application.EnableEvents = True

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Mirror data in two sheets
 

Hi Robert,

Am Sat, 29 Nov 2014 10:56:20 +0100 schrieb Claus Busch:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

if you have more than the 2 sheets into the workbook change the code to:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Intersect(Target, Sh.Range("A1:C10")) Is Nothing Then Exit Sub

Dim rngDest As Range
Dim Addr As String

Application.EnableEvents = False

Addr = Target.Address
Select Case Sh.Name
Case "Sheet1"
Set rngDest = Sheets("Sheet2").Range(Addr)
Case "Sheet2"
Set rngDest = Sheets("Sheet1").Range(Addr)
End Select
If Not rngDest Is Nothing Then rngDest = Target.Value

Application.EnableEvents = True

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Mirror data in two sheets
 

"Claus Busch" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Intersect(Target, Sh.Range("A1:C10")) Is Nothing Then Exit Sub

Dim rngDest As Range
Dim Addr As String

Application.EnableEvents = False

Addr = Target.Address
Select Case Sh.Name
Case "Sheet1"
Set rngDest = Sheets("Sheet2").Range(Addr)
Case "Sheet2"
Set rngDest = Sheets("Sheet1").Range(Addr)
End Select
rngDest = Target.Value

Application.EnableEvents = True

End Sub


Great, that works perfect. Thank you Claus!