VBA Select Sheet, Activate Sheet, and Get Activesheet

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on August 10, 2021

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.

vba activate sheet

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

Sheet1.Activate

vba activesheet

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).

vba selected sheets

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

vba select sheet

Select Worksheet by Index Number

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

Worksheets(1).Select

vba select sheet index number

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

VBA Programming | Code Generator does work for you!

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Select Activate Worksheet

 

Learn More!


<<Return to VBA Examples

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples