How to reference an excel sheet by variable

0 votes

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

enter image description here

enter image description here

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.

Feb 16, 2023 in Others by narikkadan
• 63,420 points
417 views

1 answer to this question.

0 votes

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
answered Feb 16, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
787 views
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,420 points
1,235 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
2,802 views
0 votes
1 answer

How to fix a circular reference error by if condition in excel file?

Circular reference in this context refers to ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,420 points
429 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
662 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,349 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,200 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
940 views
0 votes
1 answer

How to read a dataframe from an excel sheet containing multiple tables?

Here is one option with scikit-image (highly inspired by ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
1,718 views
0 votes
1 answer

Excel VBA: how to find a description from an AD-group

First add the 'description' property to your ...READ MORE

answered Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
506 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP