REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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.

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"
Effortlessly Manage Your Projects and Resources
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.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBALast Updated: December 21, 2022

Leave A Comment