excel - 工作表之间单元格的镜像范围

Question

表 1

  Private Sub Worksheet_change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B93")) Is Nothing Then
       If Target = Range("B93") Then
          Sheets("Sheet2").Range("A1").Value = Target.Value
       End If
    End If
    
    End Sub

表 2

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
  If Target = Range("A1") Then
    If Sheets("Sheet1").Range("B93").Value <> Target.Value Then
      Sheets("Sheet1").Range("B93").Value = Target.Value
    End If
  End If
End If

End Sub

该代码仅适用于 B93 和 A1 上的单个单元格。
我尝试将范围设置为 sheet1 上的 Range("B93:N122") 和工作表 2 上的 Range("A1:M22") 以在发生更改时镜像范围，但出现错误 13 不匹配。
目标:我想将范围(A1:M22)表 1 上的更改(两种方式)镜像到表 2，反之亦然。我缺少哪一行代码？

Answer 1

镜像范围

一个工作表的单元格中更改的值也将更改为另一个工作表的相同单元格中的相同值，反之亦然。

标准模块，例如Module1

Option Explicit

Sub MirrorWorksheets( _
        ByVal Target As Range, _
        ByVal RangeAddress As String, _
        ByVal WorksheetName As String)
    
    Dim sws As Worksheet: Set sws = Target.Worksheet
    Dim irg As Range: Set irg = Intersect(sws.Range(RangeAddress), Target)
    If irg Is Nothing Then Exit Sub
    
    Dim dws As Worksheet: Set dws = sws.Parent.Worksheets(WorksheetName) 
    
    Application.EnableEvents = False
    
    Dim iarg As Range

    For Each iarg In irg.Areas
        dws.Range(iarg.Address).Value = iarg.Value
    Next iarg

    Application.EnableEvents = True
    
End Sub

Sheet1模块

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    MirrorWorksheets Target, "A1:M22,B93:N122", "Sheet2"
End Sub

Sheet2模块

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    MirrorWorksheets Target, "A1:M22,B93:N122", "Sheet1"
End Sub