Preserve Sheet Macro to Link Cells with Sheet Name Change

Question

I have two cells named INPUT_A_1 and INPUT_A_2 in worksheets named "Sheet1" and "Sheet2" respectively, that I'm linking (changing one cell triggers the identical change in the other) with the following sheet macros which work very well:

In Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_1").Address) Then
  Sheets("Sheet2").Range("INPUT_A_2") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

and in Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_2").Address) Then
  Sheets("Sheet1").Range("INPUT_A_1") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

My problem is that owing to the syntax Sheets(sheetname).Range(rangename), if I decide to rename either or both worksheets, then I have to alter the macros accordingly. Is there some sort of workaround to this that does not involve summoning the cells by the corresponding worksheet name? This problem becomes decidedly more compelling when I have 3 or more linked cells each in a different worksheet.

Thanks

Answer 1

The "workaround" is to use the codename of the worksheet instead

Using the Code Name of a Worksheet

The best method of accessing the worksheet is using the code name. Each worksheet has a sheet name and a code name. The sheet name is the name that appears in the worksheet tab in Excel.

Changing the sheet name does not change the code name meaning that referencing a sheet by the code name is a good idea.

Answer 2

Following Storax's excellent suggestion above, here is the fix that I implemented:

In the first worksheet (which can be renamed at will):

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_1").Address) Then
  SheetFromCodeName("Sheet2").Range("INPUT_A_2") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

and in the Second worksheet (which can also be renamed at will):

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 If (Target.Address = Range("INPUT_A_2").Address) Then
  SheetFromCodeName("Sheet1").Range("INPUT_A_1") = Target.Value
 End If
 Application.EnableEvents = True
End Sub

And finally, in any module:

Public Function SheetFromCodeName(CodeName$) As Worksheet
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.CodeName = CodeName Then
           Set SheetFromCodeName = sh
           Exit For
        End If
    Next sh
End Function

The SheetName and CodeName association is according to:

If one has multiple sheets with linked cells, and any of the sheets is deleted, an On Error Resume Next should work.

Answer 3

In fact, the Sheet Index could be used instead thereby obviating the need for the SheetFromCodeName routine altogether.

The syntax in this case in the first and second sheets would be

Worksheets(2).Range("INPUT_A_2") = Target.Value

and

Worksheets(1).Range("INPUT_A_1") = Target.Value