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 SubNotes:
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
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!
