如何在 Excel 工作表之间镜像各个单元格

如何在 Excel 工作表之间镜像各个单元格

我有一张工作表,需要单个单元格镜像另一张工作表上的单个单元格,如果发生更改,反之亦然。我在超级用户上找到了以下代码:

对于工作表 1:

Private Sub Worksheet_change(ByVal Target as Range)
Sheets("Sheet2").Range("B7").Value = Target.Value
End Sub

对于工作表 2:

Private Sub Worksheet_change(ByVal Target as Range)
If Sheets("Sheet1").Range("A4").Value <> Target.Value Then
Sheets("Sheet1").Range("A4").Value = Target.Value 
End if
End Sub

但我无法让它工作。我收到一条错误消息:

运行时错误‘9’:

下标超出范围。

调试指向这一行:

Sheets("Sheet2").Range("B7").Value = Target.Value

我到底做错了什么?

谢谢!

答案1

假设我想镜像单元格Sheet1.[A4]带有电池Sheet2.[B7],我将使用以下内容:


工作表1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Sheet1 Module
    If Not Intersect(Target, [A4]) Is Nothing Then  'Update [B7] only when [A4] is updated
        MirrorCells Worksheets("Sheet2").[B7], Target
    End If
End Sub

工作表2

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Sheet2 Module
    If Not Intersect(Target, [B7]) Is Nothing Then  'Update [A4] only when [B7] is updated
        MirrorCells Worksheets("Sheet1").[A4], Target
    End If
End Sub

模块1(标准模块)

Option Explicit

Public Sub MirrorCells(ByRef oldVal As Range, ByRef newVal As Range)
    If newVal.CountLarge = 1 Then   'Check that updated range is 1 cell (paste operations)
        If Not IsError(oldVal) And Not IsError(newVal) Then      'Ignore errors
            If oldVal <> newVal Then                'If oldVal needs to be updated
                Application.EnableEvents = False    'Stop all events
                oldVal.Value = newVal.Value         'Update it (triggers a new event)
                Application.EnableEvents = True     'Turn events back on
            End If
        End If
    End If
End Sub

Run-time error '9'是因为您没有完全命名的工作表而生成的"Sheet2"

检查选项卡名称中是否有多余的空格,例如"Sheet 2", 或"Sheet2 ",或" Sheet2"

相关内容