How to reference an excel sheet by variable

Question

I'm trying to create a code that would refer to an excel sheet using a variable, but I'm stuck on the phase where I have to choose the sheet based on the variable text, which is the month abbreviation.

Any additional assistance would be very appreciated since it was the only way I could proceed to choose the desired sheet based on the monthly index.

    Dim DestWS1 As Worksheet
    Dim InputValue As Integer
    InputValue = InputBox("Please enter your month index number", "Selecting month index to generate your report")
    
    ' Print the name of the airport to the Immediate Window(Ctrl + G)

        Dim MonthIndexResult As String
        
        MonthIndexResult = _
        "=LOOKUP(" & InputValue & ",InputData!R3C17:R14C17,InputData!R3C16:R14C16)"

Set DestWS1 = ThisWorkbook.Sheets(" & MonthIndexResult & ")
DestWS1.Select

Any additional assistance would be appreciated because it was the only way I could proceed to choose the desired sheet based on the month index.

Answer 1

Identify Worksheet By Partial Name

Sub SelectByMonth()

    Dim InputString As String
    InputString = InputBox("Please enter your month index number", _
        "Selecting month index to generate your report")
    If Len(InputString) = 0 Then Exit Sub ' no input
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = wb.Sheets("InputData")
    Dim slrg As Range: Set slrg = sws.Range("Q3:Q14")
    Dim srrg As Range: Set srrg = sws.Range("P3:P14")
    
    Dim srIndex As Variant
    srIndex = Application.Match(CLng(InputString), slrg, 0)
    If IsError(srIndex) Then Exit Sub ' month (number) not found
    Dim sMonth As String: sMonth = srrg.Cells(srIndex)
    
    Dim dws As Worksheet
    
    For Each dws In wb.Worksheets
        If InStr(1, dws.Name, sMonth, vbTextCompare) > 0 Then Exit For
    Next dws
    
    If dws Is Nothing Then Exit Sub ' worksheet not foundd
    
    If Not wb Is ActiveWorkbook Then wb.Activate
    
    dws.Select

End Sub