I am trying to use the VB code from this question in Excel 2013 and it is not working. I am not familiar with VB and am most likely missing a simple step.
Private Sub Workbook_TwoWayMatch(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
Using a new workbook with worksheets named Sheet1 and Sheet2 I have tried the following in Visual Basic:
- pasting the code into the object: ThisWorkbook
- pasting the code into the objects: Sheet1 (Sheet1) and Sheet2 (Sheet2)
- pasting the code into all 3 objects
- ensuring the workbook is saved as macro enabled
- ensuring the workbook security settings are set to trust access to the VBA object model and that macros are enabled
What do I need to do in order to get this code to run?
In response to Mat's Mug's suggestion:
I put the code in a standard module as you suggested, changed the procedure to Public and modified the range. However, I still cannot get it to copy the values from the range on one sheet to the other. The code looks like this now:
Public Sub Workbook_TwoWayMatch(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:J23")) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Parent.Name) = "SHEET1" Then
Sheets("Sheet2").Range("A1:J23") = Target
Else
Sheets("Sheet1").Range("A1:J23") = Target
End If
Application.EnableEvents = True
End If
End If
End Sub