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.

excel vba select sheet variable name


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.

Select Active Sheet by Variable Name with VBA in Excel

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

Select Active Sheet by Variable Name with VBA in Excel

  • 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

Select Active Sheet by Variable Name with VBA in Excel

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

Select Active Sheet by Variable Name with VBA in Excel

  • 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:

Excel VBA to Set Variable Name for Selecting Sheet

  • Go to Developer Visual Basic.
  • Select Insert 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.

Excel VBA to Set Variable Name for Selecting Sheet

  • Click SelectSheet and press Run.

Excel VBA to Set Variable Name for Selecting Sheet

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

Excel VBA to Set Variable Name for Selecting Sheet

  • Go to File 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 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:


 

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo