Selecting a worksheet based on a cell value in Excel VBA-CodePudding

I am trying to select a particular page based on cell value U2. I am using the backend Excel names not the display names for the sheets. "Sheet11" is the sheet I am currently trying to connect to. I have tried the following codes, but getting run-time error 9, out of range.

What could I try next?

Thanks

'#1
Dim ws As Worksheet
ws = Range("U2")
Set ws = ActiveSheet

'#2
(Range("U2").Activate

'#3
Sheet11.Activate
Works but no variable

'#4
Sheets(Range("U2").Text).Activate

'#5
Sheets(Range("U2").Value).Activate

'#6
Dim GetString As String
GetString = Range("U2")
GetString.Activate

CodePudding user response：

Is this what you are looking for?

Sheets(ActiveSheet.Range("U2").Value).Select

CodePudding user response：

Reference a Worksheet By Its Code Name

Option Explicit

Sub RefByCodeName()
    
    ' Write the code name to a string variable ('wscName').
    ' Adjust the worksheet!
    Dim wscName As String: wscName = CStr(Sheet1.Range("U2").Value)
    
    ' Using the 'RefWorksheetByCodeName' function, attempt to reference
    ' the worksheet ('ws') by its code name.
    Dim ws As Worksheet: Set ws = RefWorksheetByCodeName(wscName, ThisWorkbook)
    
    ' Validate the worksheet.
    If ws Is Nothing Then
        MsgBox "No worksheet with the code name '" & wscName & "' found.", _
            vbCritical
        Exit Sub
    End If
    
    ' Continue, e.g.:
    MsgBox "Name: " & ws.Name & vbLf & "Code Name: " & ws.CodeName, _
        vbInformation
    
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      In a workbook ('wb'), references a worksheet
'               by its code name ('WorksheetCodeName').
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefWorksheetByCodeName( _
    ByVal WorksheetCodeName As String, _
    ByVal wb As Workbook) _
As Worksheet

    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If StrComp(ws.CodeName, WorksheetCodeName, vbTextCompare) = 0 Then
            Set RefWorksheetByCodeName = ws
            Exit Function
        End If
    Next ws

End Function