How to select a sheet in Excel VBA

VBA (Visual Basic for Applications) is a highly effective tool for automating tasks and improving productivity in many industries. Its primary strength is its ability to manipulate and automate tasks within Microsoft Office applications, such as Excel, Word, and Access. VBA can be used to create custom functions, automate repetitive tasks, and even create user interfaces. With VBA, users can reduce manual input, improve accuracy, and streamline complex workflows. 

In this tutorial we’ll learn how to call a sub procedure in VBA Excel. There are a few variations to the process of calling a Sub in Excel VBA. We’ll discuss the following possibilities in this tutorial;

• Selecting a sheet by its Index Number
• Selecting a sheet by Sheet’s Name

Method 1: Selecting a sheet by Index Number

Selecting a sheet can be done by using the sheet’s index number along with the Worksheet method. The index numbers start from 1 i.e., Sheet1 will have index 1, Sheet2’s index number will be 2 and so on. Follow the steps below to learn how it can be done in VBA.

Step 1 – Open VBA Editor 

• Open VBA Editor by going to the Developer tab and then pressing the Visual Basic action button.
• This can also be done by pressing the shortcut keys ALT+F11

Step 2 – Write the simple code to select sheet by index

• In the VBA Editor write the following code to select the sheet by index number.

Step 3 – Test the code from VBA Editor

• With the VBA Editor in active state, click in the boundaries of Select_Sheet_by_Index and then press the F5 shortcut key or press the play button to run the code. 
• You will see that initially the sheet3 was selected but as the code executes the Sheet1 will be selected as shown above.

Method 2: Selecting a sheet by Sheet’s Name

We can also select a sheet by using the sheet’s name along with the Worksheet method. The default sheet names are Sheet1, Sheet2 and so on. However, we can rename a sheet to give it a meaningful name as well and then use that name to select that sheet as well. Follow the steps below to learn how it can be done in VBA.

Step 1 – Open VBA Editor 

• Open VBA Editor by going to the Developer tab and then pressing the Visual Basic action button.
• This can also be done by pressing the shortcut keys ALT+F11

Step 2 – Write the simple code to select sheet by Name

• In the VBA Editor write the following code to select the sheet by index number.

Step 3 – Test the code from VBA Editor

• With the VBA Editor in active state, click in the boundaries of Select_Sheet_by_Name and then press the F5 shortcut key or press the play button to run the code. 
• You will see that initially the sheet3 was selected but as the code executes the sheet named as Data Sheet will be selected as shown above.