VBA Select Sheet, Activate Sheet, and Get Activesheet

This article will discuss the ActiveSheet object in VBA. It will also discuss how to activate, select, and go to Worksheets (& much more). Read our full VBA Worksheets Guide for more information about working with worksheets in VBA.

ActiveSheet

In VBA, ActiveSheet refers to the currently active Worksheet. Only one Sheet may be active at a time.

Activate Worksheet (Setting the ActiveSheet)

To set the ActiveSheet use Worksheet.Activate:

Worksheets("Input").Activate

The Activate Sheet command will actually “go to” the sheet, changing the visible Sheet.

The above example uses the Sheet (Tab) name.  Instead you can use the VBA code name for the worksheet:

Sheet1.Activate

ActiveSheet Name

To get the ActiveSheet Name:

msgbox ActiveSheet.name

Selected Sheets vs ActiveSheet

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

When multiple Worksheets are selected only the “top-most” Worksheet is considered active (the ActiveSheet).

Select Worksheet

If you would like to select a worksheet instead of activating it. Use .Select instead.

Select Worksheet by Tab Name

This selects a Worksheet based on it’s Sheet Tab Name

Sheets("Input").Select

Select Worksheet by Index Number

This selects a Worksheet based on it’s position relative to other tabs

Worksheets(1).Select

Select Worksheet With VBA Code Name

Sheet1.Select

Selecting worksheets by code name can prevent errors caused by worksheet name changes.

Select Current Worksheet

To select the current Worksheet, use the ActiveSheet object:

ActiveSheet.Select

 

More Activate / Select Sheet Examples

Set ActiveSheet to Variable

This will assign the ActiveSheet to a Worksheet Object Variable.

Dim ws As Worksheet

Set ws = ActiveSheet

Change ActiveSheet Name

This will change the ActiveSheet Name.

ActiveSheet.Name = "NewName"

With ActiveSheet

Using the With Statement allows you to streamline your code when working with objects (such as Sheets or ActiveSheet).

With ActiveSheet
    .Name = "StartFresh"
    .Cells.Clear
    .Range("A1").Value = .Name
End With

Notice how you don’t need to repeat “ActiveSheet” before each line of code. This can be a huge time saver when working with a long list of commands.

Loop Through Selected Sheets

The following macro will Loop through all selected sheets, displaying their names.

Sub GetSelectedSheetsName()
    Dim ws As Worksheet

    For Each ws In ActiveWindow.SelectedSheets
         MsgBox ws.Name
    Next ws

End Sub

GoTo Next Sheet

This code will go to the next Sheet. If the ActiveSheet is the last Sheet, then it will go to the first Sheet in the Workbook.

If ActiveSheet.Index = Worksheets.Count Then
    Worksheets(1).Activate
Else
    ActiveSheet.Next.Activate
End If