How to Select a Sheet Using a Variable Name with VBA in Excel (2 Methods)

Dataset Overview

Let’s use a sample dataset as an example. The dataset represents the Salesman, Product, and Net Sales of a company, spread across different sheets.

Method 1 – Select Active Sheet by Variable Name with VBA in Excel

In this method, we’ll select the active sheet using a variable name. The Active Sheet refers to the sheet we are currently working on. Follow these steps:

• Click on the Developer tab and select Visual Basic.

• The VBA window will appear.
• Click Insert and choose Module to open the Module window.

• The Module window will appear.
• Copy and paste the following code into the module:

Sub ActiveSheetSelect()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Select
End Sub

• Close the VBA window.
• Under the Developer tab, select Macros.

• Choose ActiveSheetSelect and click Run.

• It will return the sheet you were working on.

Read More: How to Rename Sheet with VBA in Excel

Method 2 – Excel VBA to Set Variable Name for Selecting Sheet

In this method, we’ll set a variable name for our desired worksheet and select that sheet using VBA. Follow these steps:

• Go to Developer tab and select Visual Basic.
• Select Insert and choose Module.
• Copy the following code and paste it into the newly appeared Module dialog box:

Sub SelectSheet()
Dim VarSheet As String
VarSheet = "Sheet2"
With ActiveWorkbook.VBProject
Worksheets(CStr(.VBComponents(VarSheet).Properties("Name"))).Select
End With
End Sub

• Close the VBA window.
• Open Sheet3 and select the Macros from the Developer tab.

• Click SelectSheet and press Run.

• You may get an error dialog box; press End.

• Go to File and select Options.
• In the Trust Center tab, select Trust Center Settings.

• In the Trust Center dialog box, go to the Macro Settings tab.
• Check the box for Trust access to the VBA project object model and press OK.

• Select Developer and choose Macros.
• Choose SelectSheet and press Run.

• It will return Sheet2 even though we were working on Sheet3.

Read More: How to Search Sheet Name with VBA in Excel

Download Practice Workbook

You can download the practice workbook from here: