Tutorial Details
Downloadable Files: Excel File
Introduction to Programming Macros in Excel
First Steps
Getting and Inputting Data
Adding Logic to Macros
Loops
UDF- User Defined Functions
Speeding Up Macros
Security

Activate or Navigate to a Worksheet using Macros VBA in Excel

Add to Favorites
Author:

Make a particular worksheet visible using a macro in Excel.

This is called activating a worksheet and is rather easy to do.

Sections:

Activate Worksheet by Name

Activate Worksheet by Index Number

Notes

Activate Worksheet by Name

Let's navigate to, activate, the worksheet called "Sheet2".

Worksheets("Sheet2").Activate

08008f3433d614b4eb713fdcde4e01eb.jpg

Worksheets("Shee2") is how we reference the sheet to which we want to navigate.

Sheet2 is the name of the sheet to which we want to navigate. Make sure to surround it with double quotation marks.

Activate is what actually takes the user to that worksheet.

Putting it all together, we get: Worksheets("Sheet2").Activate

It's as simple as that.

Activate Worksheet by Index Number

This time, let's navigate to the second worksheet using its index number.

All we do is to replace "Sheet2" from the last example with 2.

Worksheets(2).Activate

3e8969b16e6f131afc29445b0f9a02ac.jpg

2 is the index number of the desired worksheet and Activate is what takes us to the desired worksheet.

This example can be confusing if you are not used to using index numbers, but index numbers are really helpful when you have to do something like loop through all of the worksheets in the workbook.

Notes

Basically, just reference the desired worksheet, however you want, and then type .Activate after it.

You should not use the Activate feature to navigate to a worksheet so that you can get data from it or put data into it. This is very bad Macro/VBA design and it will make your life hell later on. To get data from separate worksheets, read this tutorial: Select Data from Separate Worksheets with Macros VBA in Excel.

This feature should be used when you want the user to end up on a specific worksheet.

Make sure to download the sample file attached to this tutorial so that you can see this macro in Excel and work with it.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File