Select sheet based on cell value
 

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

Select sheet based on cell value
 

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)

"Dan Mills" wrote in message
m...
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

Select sheet based on cell value
 

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Dan Mills" wrote in message m...
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