Excel/VBA Active sheet: Learn to Access, Rename, Activate with 4 Examples

Working with Excel Active Worksheet using VBA

In this tutorial, you will learn how to make a non-active sheet in Excel an active sheet by using VBA code.

The examples will cover how to access an active sheet name, rename an active sheet, and make a sheet active and removing as well.

For the information, if a sheet is active, the actions you perform like macro actions, accessing data, etc. without referring to a sheet name/number will be performed on that active sheet.

An example of getting the name of Active Sheet in VBA

Let us start with a simple example of getting the active sheet name and displaying it in a message box.

Our sample Workbook has the following sheets:

In VBA, the Application object has a property ActiveSheet which enables us to work with the active sheet.

The ActiveSheet has a method called “Name” that can be used to get the active sheet name.

Display active sheet name in alert example

Step 1:

Place a command button in a sheet:

Step 2:

Double-click on the button in “Design Mode” to open the VBA editor

Step 3:

Write this line of code in the click event of the button:

MsgBox “Active Sheet Name = ” & ActiveSheet.Name

Save the code and get back to the sheet containing that button.

Step 4:

Click on the button after unselecting “Design Mode” and it should display our active sheet name in an alert as shown below:

Making a sheet active in VBA example

To make a sheet active in VBA, you may use the Worksheet object with its Activate method.

Suppose, we were on the “test1” sheet in our sample Workbook. We want to make “Sheet5” active. This is how you can do it:

Worksheets(“Sheet5”).Activate

Let us have a look at this visually.

Step 1:

We placed a command button on the “test1” sheet.

Step 2:

Double-click the button to open the VBA editor and write these lines of code on the click event of button:

Save the code.

Remember to Select “Design Mode” to open the editor.

Step 3:

Click on the button and it should display two message boxes. First, the current active sheet name i.e. “test1” in our sample sheet.

Then this line of code executes:

Worksheets(“Sheet5”).Activate

And again a message box displays that shows the following:

Renaming the Active sheet example

For renaming the active sheet, simply use the ActiveSheet.Name i.e.

ActiveSheet.Name = “New Name”

To demonstrate that, consider our sample Workbook shown in the first example, let us change the name of the active sheet.

“Sheet5” is our active sheet and we want to rename it to “Product Information”. See below how.

Step 1:

Place a command button in Sheet5 and open the VBA editor by double-clicking on the button.

Step 2:

Write this code on the click event of the button:

Save the code.

Step 3:

Click the button and it will change the Sheet5 name as shown below:

Removing the Active sheet example

Removing the Active sheet is also simple. Just use:

ActiveSheet.Delete

method and active sheet will be removed.

For example, we wrote this code line to the command button click event. As you click the button, Excel will trigger a warning message.

Pressing Delete will remove the active sheet, in our case “Product Information” sheet as shown below: