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.
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
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!