In this Article
- Selected Sheets vs ActiveSheet
- Select Worksheet
- More Activate / Select Sheet Examples
- VBA Coding Made Easy
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.
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:
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:
To get the 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).
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
Select Worksheet by Index Number
This selects a Worksheet based on it’s position relative to other tabs
Select Worksheet With VBA Code Name
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:
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"
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!