VBA Select Sheet, Activate Sheet, and Get Activesheet
In this Article
This article will discuss the ActiveSheet object in VBA. It will also discuss how to activate, select, and go to Worksheets (& much more).
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:
|
1 |
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:
|
1 |
Sheet1.Activate |
ActiveSheet Name
To get the ActiveSheet Name:
|
1 |
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
|
1 |
Sheets("Input").Select |
Select Worksheet by Index Number
This selects a Worksheet based on it’s position relative to other tabs
|
1 |
Worksheets(1).Select |
Select Worksheet With VBA Code Name
|
1 |
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:
|
1 |
ActiveSheet.Select |
More Activate / Select Sheet Examples
Set ActiveSheet to Variable
|
1 2 3 |
Dim ws As Worksheet Set ws = ActiveSheet |
Change ActiveSheet Name
|
1 |
ActiveSheet.Name = "NewName" |
With ActiveSheet
Using With allows you to streamline your code when working with objects (such as Sheets or ActiveSheet).
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 |
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!
Did you find this VBA tutorial useful? Then share it with your friends and colleagues:
