Dropdown selection that triggers macro

[Berrett]

I want to have users select a value from a dropdown box, then have their selection take them to one of twenty or so worksheets. Can someone help me write the code that would do this? Thanks. BR

 

[Derek]

Hi there

Here are 2 examples for you. They both assume you have a data validation dropdown list in A1. The first code is where your list is the names of the worksheets. The second code is where your list is of column G of a table in G1:H20 - and the sheet names are in column H. Amend to suit your own situation:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
On Error Resume Next
Sheets(Target.Value).Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
On Error Resume Next
Dim X As String
X = WorksheetFunction.VLookup(Target.Value, Range("G1:H20"), 2, False)
Sheets(X).Select
End Sub

To enter one of these codes, copy it, right click the sheet tab, left click view code and paste it in the large code window.

regards
Derek