How to synchronize drop-down lists in multiple worksheets in Excel?

Suppose you have drop-down lists on several worksheets in a workbook that contain exactly the same drop-down items. Now you want to synchronize the drop-down lists across worksheets so that once you select an item from a drop-down list in one worksheet, the drop-down lists in other worksheets are automatically synchronized the same selection. This article provides a VBA code to help you solve this problem.

Synchronize drop-down lists in multiple worksheets with VBA code

Synchronize drop-down lists in multiple worksheets with VBA code

For example, the drop-down lists are in five worksheets named Sheet1, Sheet2, ..., Sheet5, to synchronize the drop-down lists in other worksheets according to the drop-down selection in Sheet1, please apply the following VBA code to get it done.

1. Open Sheet1, right click the sheet tab and select View Code from the right-click menu.

2. In the Microsoft Visual Basic for Applications window, paste the following VBA code into the Sheet1 (Code) window.

VBA code: Synchronize drop-down list in multiple worksheets

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20220815
    Dim tSheet1 As Worksheet
    Dim tRange As Range
    Dim xRangeStr As String
    On Error Resume Next
    If Target.Count > 1 Then Exit Sub
    
    xRangeStr = "A2:A11"

    Set tRange = Intersect(Target, Range(xRangeStr))
    If Not tRange Is Nothing Then
        xRangeStr = tRange.Address
        Application.EnableEvents = False
        Set tSheet1 = ActiveWorkbook.Worksheets("Sheet2")
        tSheet1.Range(xRangeStr).Value = Target.Value
        Set tSheet1 = ActiveWorkbook.Worksheets("Sheet3")
        tSheet1.Range(xRangeStr).Value = Target.Value
        Set tSheet1 = ActiveWorkbook.Worksheets("Sheet4")
        tSheet1.Range(xRangeStr).Value = Target.Value
        Set tSheet1 = ActiveWorkbook.Worksheets("Sheet5")
        tSheet1.Range(xRangeStr).Value = Target.Value
        Application.EnableEvents = True
    End If
    
End Sub

Notes:

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, when you select an item from the drop-down list in Sheet1, the drop-down lists in the specified worksheets will be synchronized automatically to have the same selection. See the below demo.

Demo: Synchronize Drop-Down Lists In Multiple Worksheets In Excel