Selecting a worksheet based on a cell value in Excel VBA

user_19909735 0
Viewed 53

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
vba excel
3 Answers

Is this what you are looking for?

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

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

    ' Make sure the workbook is active.
    If Not ThisWorkbook Is ActiveWorkbook Then ThisWorkbook.Activate 

    ' Select the worksheet.
    ws.Select         

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
user_98140690

Sheets have 3 ways of referring to them:

  1. Name (What you see on the tab at the bottom)
  2. Index (A number representing the sheet's position in the workbook, i.e. this will change if you change the position of a sheet)
  3. CodeName (This is what you refer to as the back end excel name)

You can select a sheet using any of these. If I had a workbook with tabs named after months in Jan to Dec order and I haven't changed the code names the code to select November using each would be as follows: 1.Name Worksheets("November").Select 2.Index Worksheets(11).Select 3.CodeName Sheet11.Select Unlike name and index you can refer to the codename directly as it is an object.

the following code uses the index to loop through the sheet codenames comparing them to the value in U2 when it is the same as U2 it will select the sheet.

Dim lCodeName As String
Dim lWSCount, lCount As Integer
lCodeName = ActiveSheet.Range("U2").Value
lWSCount = ActiveWorkbook.Worksheets.Count

For lCount = 1 To lWSCount
If Worksheets(lCount).CodeName = lCodeName Then
Worksheets(lCount).Select
Else
End If
Next
user_198035670
Related