在工作簿中链接Excel单元格 [英] interlink excel cell within a workbook

问题描述

我有一个Excel工作簿，其中有4个工作表，其中有两个单元格名称开始日期和结束日期，其值在所有4个工作表中都应该相同，如果我要更改其他任何三个工作表中的值，工作表会自动更新该值.反之亦然.

I have Excel Workbook in which there are 4 sheet which has two cell name start date and end date, whose values should be same across all 4 sheet, I want that If I change the value in anyone of the sheets the other three sheets automatically update that values. And vice versa.

推荐答案

使用 Workbook_SheetChange 事件，以在每个工作表中的任何一个单元发生更改时更新相同的单元.

Use the Workbook_SheetChange event to update the same cells on every worksheet if any one of the cells changes.

例如，如果每个工作表都具有命名范围 start_date 和 end_date (其中范围仅限于该工作表)，则对任何 start_date进行的更改或 end_date 在任何工作表上的范围 都会更新所有其他工作表上的相应范围.

For example, if each sheet has the named ranges start_date and end_date (where their scope is limited to that sheet only), changes made to any start_date or end_date range on any sheet will update the corresponding range on all the other sheets.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo SafeExit
    Application.EnableEvents = False
    Dim ws As Worksheet

    With Sh
        If Not Intersect(Target, .Range("start_date")) Is Nothing Then
            For Each ws In Worksheets
                ws.Range("start_date").Value = Target.Value
            Next ws
        End If
        If Not Intersect(Target, .Range("end_date")) Is Nothing Then
            For Each ws In Worksheets
                ws.Range("end_date").Value = Target.Value
            Next ws
        End If
    End With

SafeExit:
    Application.EnableEvents = True
End Sub

---

如果您是通过单元格的地址而不是通过定义的名称来引用这些单元格的，那么这样的方法可能会起作用:

---

If you are referring to the cells by their address and not by a defined name, something like this could work:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo SafeExit
    Application.EnableEvents = False
    Dim ws As Worksheet

    With Sh
        ' "A1" is the start date, change as needed
        If Not Intersect(Target, .Range("A1")) Is Nothing Then
            For Each ws In Worksheets
                ws.Range("A1").Value = Target.Value
            Next ws
        End If
        ' "B1" is the end date, change as needed
        If Not Intersect(Target, .Range("B1")) Is Nothing Then
            For Each ws In Worksheets
                ws.Range("B1").Value = Target.Value
            Next ws
        End If
    End With

SafeExit:
    Application.EnableEvents = True
End Sub

---

此代码在VBA编辑器的 ThisWorkbook 模块中.

这篇关于在工作簿中链接Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！