镜像片材之间的单元格范围

Question

Sheet1

  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

Sheet2

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 上的单个单元格。

我尝试将范围设置为工作表 1 上的范围（“B93：N122”）和工作表 2 上的范围（“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