excelvba

how to select worksheet name from cell


I want to select some worksheet name that name worksheet in store in some cell but, i got error like this vba error message

here is my vba code

Sub selAct()
Dim loc as string
loc = std.range("B2") 'text that refer my worksheet name 

'error in this code
ThisWorkbook.Worksheets(loc.Name).Select

End Sub


Solution

  • Reference Worksheets By (Tab) Name vs Code Name

    Sub WorksheetByTabName()
        
        Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
        Dim TabName As String: TabName = std.Range("B2").Value
        
        Dim ws As Worksheet
        
        On Error Resume Next
            Set ws = wb.Worksheets(TabName)
        On Error GoTo 0
        
        If ws Is Nothing Then
            MsgBox "There is no worksheet with the (tab) name """ & TabName _
                & """ in workbook """ & wb.Name & """!", vbExclamation
            Exit Sub
        End If
        
        ' Before selecting a sheet, make sure its workbook is active:
        wb.Activate
        ws.Select
    
    End Sub
    
    Sub WorksheetByCodeName()
        
        Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
        Dim CodeName As String: CodeName = std.Range("B2").Value
        
        Dim ws As Worksheet
        
        On Error Resume Next ' prevent error if worksheet doesn't exist
            Set ws = wb.Worksheets(wb.VBProject.VBComponents(CodeName) _
                .Properties("Name").Value)
        On Error GoTo 0
        
        If ws Is Nothing Then
            MsgBox "There is no worksheet with the code name """ & CodeName _
                & """ in workbook """ & wb.Name & """!", vbExclamation
            Exit Sub
        End If
        
        ' Before selecting a sheet, make sure its workbook is active:
        wb.Activate
        ws.Select
    
    End Sub