VBA ActiveSheet Object helps to refer the currently activated sheet in the active workbook. We can get all properties and methods of the ActiveSheet using VBA. Let us see the useful of the examples on Excel VBA ActiveSheet Object.
ActiveSheet VBA in Excel

What is Active Sheet in Excel VBA

Worksheet which is currently activated in the Active Workbook and Active Window is referred as Active Sheet. You can make any Worksheet as Active Worksheet by Activating a Worksheet. You can use Activate Method of Worksheet to activate a sheet using Excel VBA.

Sheets("SheetName").Activate

Set a Worksheet as Active Sheet in VBA

You can use Activate method to set worksheet as active sheet in VBA. For example, you can set the Sheet 3 as active sheet by calling the Activate Method.

Sheets(3).Activate

Reading the Data from ActiveSheet

We can use the VBA to read the data from ActiveSheet Object. We can use all the methods and properties of a worksheet Object. For example, the following example get the data from ActiveSheet and show it in a message box.

Sub sbGetDataFromActiveSheet()
MsgBox ActiveSheet.Range("D5")
End Sub

The code statement ActiveSheet.Range(“D5”) is reading the data from Range D5 of ActiveSheet. It is not mandatory to specify the ActiveSheet befor Range object in the above macro. Range(“D5”) also refer the data from ActiveSheet.

Then, when do we use ActiveSheet Object in Real-time projects?

Uses of ActiveSheet in VBA Development

We deal with multiple worksheets while automating an Excel Task. We can simply set the ActiveSheet to a variable and keep it fro future reference.

For example: Let us say, we have 3 worksheets in the Workbook and currently you have activated Sheet 2 and You wants to move into Sheet 3 and refer the Sheet2 Data. You can use the ActiveSheet Object in this scenario.

Sub sbGetDataFromActiveSheet()
'You wants to work on Sheet 2
Sheets(2).Activate
'Reading the Data from ActiveSheet Range A1
K = Range("A1")

'Now you wants to move into Sheet 3.
'Let us set the ActiveSheet into a temporary variable before activating the sheet 3
Set sht = ActiveSheet

'Now activate Sheet 3
Sheets(3).Activate

'Enter Data from Range D3 of Sheet 2 in Range A1 of Sheet 3
Range("A1") = sht.Range("D3")
End Sub

Get the Name of the ActiveSheet

You can use the .Name property of the ActiveSheet object to return the ActiveSheet Name using VBA.

Sub sbActiveSheetName()
MsgBox ActiveSheet.Name
End Sub

Copy the Data from Other Sheet and Paste in the ActiveSheet

We can copy the data from a worksheet (it can be the same sheet or a different sheet)  and paste in the ActiveSheet.

Sub sbCopyFromOtherSheetAndPasteInActiveSheet()
Sheets(2).Activate
Sheets(3).Range("A1:G25").Copy
Range("G1").Select
Activeheet.Paste
End Sub

The above macro will Activate the Sheet 2. And copy the data from Sheet 3 and Paste at Range G1 of the ActiveSheet.

Count Shapes in ActiveSheet

The following macro will return the number of shapes in ActiveSheet.


Sub sbCountShepsInActiveSheet()
MsgBox Activeheet.Shapes.Count
End Sub

Count Charts in ActiveSheet

The following macro will return the number of Chart Objects in ActiveSheet.

Sub sbCountChartsInActiveSheet()
MsgBox Activeheet.ChartObjects.Count
End Sub

Protect in ActiveSheet using VBA

You can use the Protect Method of ActiveSheet to password protect the Sheet.

Sub sbProtectSheet()
    ActiveSheet.Protect "password", True, True
End Sub

UnProtect: You can use unprotect method of Activesheet to unprotect the sheet.

    ActiveSheet.UnProtect "password"

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: August 6th, 2019Categories: Excel VBA

Share This Story, Choose Your Platform!

About the Author: PNRao

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).