VBA ActiveSheet – Excel Active Sheet Object

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"