Select sheet based on cell value

[Dan Mills]

Could someone help me with some code? I have a workbook with twelve
worksheets named A, B,... I, J, Formula and Menu. On the Formula
sheet, I have the following range of cells:

A B C
1 10 A 30
2 20 B
3 30 C
....
9 90 I
10 100 J

Based on the value of cell formula!C1 (which is 30) I would like to
run a macro from the Menu sheet that will look up 30 in the range
Formula!A1:B10 and return the value "C" to the macro that will select
the worksheet named "C". Or, if there is a better way please let me
know.

TIA,

Dan

 

[Bob Phillips]

Dan,

This is what you want

On Error Resume Next
Worksheets(WorksheetFunction.VLookup(Range("C1"), Range("A1:B10"), 2,
False)).Activate


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

Try this Dan

I use the sheet name "menu" in this example

Sub test()
Dim Shname As String
With Sheets("Menu")
Shname = Application.WorksheetFunction.VLookup(.Range("C1").Value, _
.Range("a1:b10"), 2)
End With
On Error Resume Next
Sheets(Shname).Select
On Error GoTo 0
End Sub