find the sheet name based on the cell value

[rvkcools]

i have an employee names from range("c24") till empty cell the prog works. if the employee name sheet is not there then the prog popup a msg box, saying "no sheet" and continue finding with other employee name

i tried this but it throws error wen sheet of a particular employee name is not available

Sub emptest()
Dim tempname As String
Worksheets("team").Select
Range("c24").Select
Do While ActiveCell.Value <> ""
tempname = ActiveCell.Value
If ActiveCell.Value = tempname Then
Worksheets(tempname).Select
Else
MsgBox "no sheet"
End If
Worksheets("team").Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

please help me

 

[Andrew Poulsom]

There's a SheetExists function you can use here:

Spreadsheet Page Excel Tips: Some Useful VBA Functions

 

[kevatarvind]

try below code

Sub SelectSheetFromCellValue()
    Dim strSheet As String
    Sheets("team").Select
    Range("C24").Select
    Do While ActiveCell.Value <> ""
    Do While ActiveCell.Value <> ""
    strSheet = ActiveCell
    On Error GoTo Cycel:
    Sheets(strSheet).Select
    Sheets("team").Select
    ActiveCell.Offset(1, 0).Select
    Loop
Cycel:     MsgBox "No Sheet"
Sheets("team").Select
    ActiveCell.Offset(1, 0).Select
    Loop
    
End Sub

 

[kevatarvind]

try one more

Sub SelectSheetFromCellValue()
    Dim strSheet As String
    Sheets("team").Select
    Range("C24").Select
    Do While ActiveCell.Value <> ""
    strSheet = ActiveCell
    On Error Resume Next
    Sheets(strSheet).Select
    If Err.Number = 9 Then MsgBox "Sheet Not found."
    Sheets("team").Select
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub