How to Select Sheet by Variable Name with VBA in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we have to select a particular sheet from multiple worksheets in the Excel workbook. We can call the sheet by its default Code Name or by using a Variable Name. In order to select a sheet using the variable name, we must set the variable name first. In this article, we will show you the ways to select a sheet by using a variable name with VBA in Excel.

To illustrate, we will use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company shown in different sheets.

excel vba select sheet variable name


How to Select Sheet by Variable Name with VBA in Excel: 2 Ways

1. Select Active Sheet by Variable Name with VBA in Excel

In our first method, we’ll select the active sheet using a variable name. By Active Sheet, we mean the sheet on which we are working. Therefore, follow the steps below to select the active sheet by variable name with VBA in Excel.

STEPS:

  • First, select Visual Basic under the Developer tab.

Select Active Sheet by Variable Name with VBA in Excel

  • As a result, the VBA window will pop out.
  • Then, select Module from the Insert tab.

Select Active Sheet by Variable Name with VBA in Excel

  • The Module window will appear.
  • Copy the following code and paste it into the box.
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.
  • Next, select Macros under the Developer tab.

Select Active Sheet by Variable Name with VBA in Excel

  • The Macro dialog box will emerge.
  • Select the ActiveSheetSelect and press Run.

  • Finally, it’ll return the sheet that we were working on.

Read More: How to Rename Sheet with VBA in Excel


2. Excel VBA to Set Variable Name for Selecting Sheet

In our previous method, we applied the VBA code to return the sheet that we were already working on. In this method, we’ll set a Variable Name for our desired worksheet and select that worksheet using the variable name with VBA. So, learn the following process to perform the task.

STEPS:

  • Firstly, we’ll set the Variable Name for Sheet2 and use that variable name to select the sheet.

Excel VBA to Set Variable Name for Selecting Sheet

  • Go to Developer Visual Basic.
  • Next, select Insert Module.
  • Copy the below code and paste it into newly appeared the 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

  • After that, close the VBA window.
  • Open Sheet3 and select the Macros from the Developer tab.

Excel VBA to Set Variable Name for Selecting Sheet

  • As a result, the Macro dialog box will pop out.
  • Click SelectSheet and press Run.

Excel VBA to Set Variable Name for Selecting Sheet

  • After pressing Run, you may get an error dialog box like it’s shown in the following picture.

Excel VBA to Set Variable Name for Selecting Sheet

  • To solve the issue, press End.
  • Then, go to File Options.
  • From the Trust Center tab, select Trust Center Settings.

  • The Trust Center dialog box will appear.
  • Select the Macro Settings tab.
  • Next, check the box for Trust access to the VBA project object model and press OK.

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

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

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


Download Practice Workbook


Conclusion

Henceforth, you will be able to select a sheet by using the variable name with VBA in Excel using the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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