VBA Code to pull records based on user selection

[thatlem]

I have a dropdown list generated from data validation that allows a user to pull various sheets based on the list selection in field d17 of home sheet. A macro "Go" button executes the code on click.

If Sheets("Home").Range("d17") = "3B" Then
Sheets("Home").Visible = True
Sheets("3B Summary").Visible = True
Sheets("Action Plan Template").Visible = True
Else

However, I have now been requested to add about 50 units instead of the 10 I have, and would prefer to build one set of code instead of 50. I had thought something along the lines of:

If Sheets("Home").Range("d17") = Me Then
Sheets("Home").Visible = True
Sheets(Me & " Summary").Visible = True
Sheets("Action Plan Template").Visible = True
Else

Where Me refers to the unit select, and Me & " Summary" would translate to 3B Summary when 3B was selected. A similar option worked in Access, but I get an 438 error indicating Object is not supported.

Does anyone have any suggestions???
Thanks

 

[Kiwiman]

Howzit

Try

Dim strSheet as string

strSheet = Sheets("Home").Range("d17").value
strsheet = strsheet & "  Summary"


Sheets("Home").Visible = True
Sheets(strsheet).Visible = True
Sheets("Action Plan Template").Visible = True

 

[thatlem]

works great, thanks