Code to determine name of Worksheet

[Ant]

Probably a bit of an easy one...I have a selection of hidden worksheets which
appear in a drop down box and linked to a cell with a vlookup. I than have a
macro button which, once clicked, opens a worksheet. I want to add some code
that opens up the result of the vlookup determined by the dropdown box. For
example, if I select Sheet5 from the drop down box which in turn becomes the
sheet name in the vlookup cell, when I click the Macro button, Sheet5 opens.
Currently I have Sheets("Sheet1").Select in the macro which obviously will
only open Sheet1. I have done this before but I can't remember the code.

 

[Bob Phillips]

Is the drop-down data validation? If so, it is just the cell the DV is in

Sheets(Range("H10").Value).Select

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

I'm not clear on why you have a VLOOKUP. If the validation list is a list of
available worksheet names and the list is located in A1, use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[A1]) Is Nothing Then
Sheets(Target.Value).Select
End If
End Sub

Right-click on the worksheet tab, go to View Code, and past in the code above.

HTH
Jason
Atlanta, GA

 

[Ant]

Thanks Bob,

Not sure I need Data Validation. Once the user selects the choice from the
drop down box, that choice appears in a cell ie A:1. (via a vlookup). All I
need is code that recognises what is in that cell.

ie Sheets("What is in cell A:1").Select

 

[Ant]

Bob,

I tried that code and it works perfectly. however it only works if the
worksheet it is looking for is not hidden. Do you know what the additional
code is to unhide and select the Sheet name in eg cell "H10".

 

[Dave Peterson]

You may want to check to make sure that the worksheet actually exists, too:

Option Explicit
Sub testme()

Dim testWks As Worksheet

Set testWks = Nothing
On Error Resume Next
Set testWks = Sheets(ActiveSheet.Range("H10").Value)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Doesn't exist!"
Else
With testWks
.Visible = xlSheetVisible
.Select
End With
End If

End Sub

 

[Ant]

Thanks Dave - that works a treat. Good man!

You may want to check to make sure that the worksheet actually exists, too:

Option Explicit
Sub testme()

Dim testWks As Worksheet

Set testWks = Nothing
On Error Resume Next
Set testWks = Sheets(ActiveSheet.Range("H10").Value)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Doesn't exist!"
Else
With testWks
.Visible = xlSheetVisible
.Select
End With
End If

End Sub