---Need Help with Sheetchange---

[jaf97a]

Hello,

I am trying to do a sheetchange across multiple workbooks -(example- when I type data with in this range A1:G10 of Sheet1 in Book1 I want that same data to show up in that same cell of Sheet1 in Book2.

AND vice versa (Book2 to Book1 with the same sheet and range)


I can get it to work between sheets in the same workbook but can't seem to get it to work for the same sheets between workbooks. Here is the code I use to get it to work between sheets in the Same workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(Sh.Name) = "SHEET1" Or UCase(Sh.Name) = "SHEET2" Then
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Parent.Name) = "SHEET1" Then
Sheets("Sheet2").Range("A1") = Target
Else
Sheets("Sheet1").Range("A1") = Target
End If
Application.EnableEvents = True
End If
End If
End Sub

Thanks in advance for any help on this matter!

[Bob Phillips]

[vba]

Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const BOOK1 As String = "Book1.xls"
Const BOOK2 As String = "Book2.xls"
If Sh.Parent.Name = BOOK1 Or Sh.Parent.Name = BOOK2 Then

If UCase(Sh.Name) = "SHEET1" Then
If Not Application.Intersect(Target, Sh.Range("A1")) Is Nothing Then
Application.EnableEvents = False
If Sh.Parent.Name = BOOK1 Then
Workbooks(BOOK2).Worksheets("Sheet1").Range("A1") = Target
Else
Workbooks(BOOK1).Worksheets("Sheet1").Range("A1") = Target
End If
Application.EnableEvents = True
End If
End If
End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[mdmackillop]

In Book 1
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:G10")) Is Nothing Then
Workbooks("Book2.xls").Sheets(1).Range(Target.Address).Value = Target.Value
End If
Application.EnableEvents = True
End Sub
[/VBA]

Same for Book 2 but change the Workbooks line