interlink excel cell within a workbook

Question

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.

Answer 1

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

---

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

---

This code goes in the ThisWorkbook module in the VBA editor.

Answer 2

You don't need VBA for this but rather named ranges. Put your cursor in the input cell, say E5, for start_date and then click in the named range box (in the upper left corner of the worksheet grid, directly above column A). Type start_date over E5 and hit Enter. Now, cell E5 is named start_date. If you use =start_date anywhere else in the workbook, it will refer to the current contents of cell E5. If you want to edit the named ranges in any way, go to the ribbon menu FORMULAS -> Name Manager.

If you want to do this with VBA for the sole purpose of learning VBA, I would recommend taking a VBA course instead. Udemy has some good ones that often go on sale for about $10, and I'm sure that there are plenty of free resources elsewhere as well.