Active Sheet & Select Sheet in Excel VBA

In this post, you’ll be learning how you can select a worksheet and activate it using Excel VBA.

ActiveSheet in Excel

The term ActiveSheet, refers to the currently active Worksheet in Excel VBA.

Let’s see in detail about the active sheet command.

To run the code,

• Under the developer tab, click visual basic

• Click the insert option and choose module
• Enter your code and click run.

How to Activate or Set the WorkSheet in Excel VBA?

To set the ActiveSheet use Worksheet.Activate:

Worksheets("Sheetname").Activate

This sheet will activate a new worksheet in the current workbook.

If you want to activate an existing sheet,

Sheet1.Activate

ActiveSheet Name

To get the ActiveSheet Name, use the property ActiveSheet.name in Excel VBA.

msgbox ActiveSheet.name

Selected Sheets vs ActiveSheet in Excel VBA

At any point in time, only one Sheet can be the ActiveSheet in Excel VBA.But, multiple Worksheets can be selected at once.

If multiple Worksheets are selected, then the Worksheet with top most priority is considered as active sheet.

Select Worksheet

If you want to select a worksheet instead of activating it you can use .select  in the above codes instead of activate command.

Select Worksheet by Tab Name

This code line selects a Worksheet based on its Sheet Tab Name

Sheets("Select sheet).Select

Select Worksheet by Index Number

This code line selects a Worksheet based on its position relative to other tabs

Worksheets(1).Select

Select Worksheet with VBA Code Name

To select worksheet using VBA code name, enter the following code

Sheet1.Select

Selecting worksheets by code name will prevent errors.

Select Current Worksheet

To select the current Worksheet, you can use the ActiveSheet object:

ActiveSheet.Select

Change ActiveSheet Name

To change the active sheet name

ActiveSheet.Name = "New active sheet"

GoTo Next Sheet

To go to next sheet, enter the following code. If the active sheet is the last sheet, this command will go to the first sheet.

If ActiveSheet.Index = Worksheets.Count Then

    Worksheets(1).Activate

Else

    ActiveSheet.Next.Activate

End If