Private Sub Worksheet_Calculate()
On Error GoTo endit
Application.EnableEvents = False
With Me.Range("B2")
If .Value < "" Then
Select Case .Value
Case "COC"
Sheets("COC").Select
Case "BVO"
Sheets("BVO").Select
'more Cases here
End Select
End If
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the "Main" sheet tab and "View Code". Copy/paste the above
into that sheet module.

Edit to suit..........add more Cases..........then Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP


On Thu, 26 Feb 2009 00:33:01 -0800, Sunnyskies
wrote:

I need a macro that will open a worksheet based on the worksheet name in a
cell.
I have four worksheets:-COC, BVO, Vehicles and Reports
Now on the Main sheet in cell B2 is a formula which based on criteria will
vlookup either COC or BVO or Vehicle or Reports.
Once the name appears in B2 I want that applicable sheet to be viewed.