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?
- Set a Worksheet as Active Sheet in VBA
- Reading the Data from ActiveSheet
- Uses of ActiveSheet in VBA Development
- Get the Name of the ActiveSheet
- Copy the Data from Other Sheet and Paste in the ActiveSheet
- Count Shapes in ActiveSheet
- Count Charts in ActiveSheet
- Protect in ActiveSheet using VBA
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.
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.
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.