ActiveSheet

One of the most common situations where VBA is utilized is in worksheet operations.

To retrieve information from a specific sheet in Excel or output calculation results to a sheet, you first need to obtain the target sheet.

VBA provides multiple methods to retrieve sheets, but this time we will explain the method using the ActiveSheet property.

How to Use the ActiveSheet Property

The ActiveSheet property is used to retrieve the currently active sheet.

A typical usage is as follows:

Dim ws As Worksheet
Set ws = ActiveSheet
MsgBox ws.Name

When the above code is executed, the name of the currently active sheet is displayed in a message box.

Since the ActiveSheet property is a property of the Application class, you can retrieve it simply by writing ActiveSheet in the code, making it very easy to obtain the sheet.

When assigning it to a variable, prepare a variable of type Worksheet and use the Set statement to assign it.

ActiveSheet returns the active sheet of the active workbook. This is not necessarily the workbook that contains the macro, so it may point to an unintended sheet, for example right after opening another workbook. Also, if the active sheet is a chart sheet, assigning it to a Worksheet variable causes an error.

Alternative to ActiveSheet

When you work with a specific sheet, retrieving it by name is more reliable.

ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Now

The methods for retrieving a sheet are compared on the following page.

Examples

Print the Currently Active Sheet

Private Sub PrintActiveSheet()
    ActiveSheet.PrintOut
End Sub

Input a Value into a Cell of the Currently Active Sheet

Private Sub InputValueToActiveSheet()
  ' Input the current date and time into cell A1 of the currently active sheet
  ActiveSheet.Range("A1").Value = Now
End Sub