Cara menggunakan setactivesheet by name

When you add a new sheet in a workbook, you have the option to name it. But you can also rename it any time using the name property of the worksheet. In this tutorial, we will look at different ways to rename a sheet or multiple sheets using a VBA code.

Steps to Rename a Sheet using a VBA Code

1. First, define the sheet that you want to rename using the worksheet object.
2. After that, you need to use (.Name) to access the name property that you want to change.
3. Next, type an equal sign to that to tell VBA the value you want to use to rename the sheet.
4. In the end, type the name of the sheet that you want to use.

Get ActiveSheet Name

This will display the ActiveSheet name in a message box:

MsgBox ActiveSheet.Name

Get Sheet Name by index Number

This will display the first worksheet name in a message box:

MsgBox Sheets(1).Name

This will display the name of the last worksheet in the workbook:

MsgBox Sheets(Sheets.Count).Name

Get Sheet Name by Code Name

In the VBA Editor, there is an option to change the “code name” of a Sheet. The code name is not visible to the Excel user and can only be seen in the VBA Editor:

In VBA, when working with Sheets, you can reference the usual Tab name:

Sheets("TabName").Activate

or the VBA code name:

CodeName.Activate

Referencing the code name is desirable in case the Sheet tab name ever changes. If you allow you Excel user access to changing sheet names you should reference the code name in your VBA code so that a Sheet tab name mismatch doesn’t cause an error. Sheet code names are discussed in more detail here.

To get the Sheet name using the VBA Code name, do the following:

MsgBox CodeName.Name

Rename Sheet

You can rename Sheets by adjusting the name property of the Sheets or Worksheets object.

Rename ActiveSheet

ActiveSheet.Name = "NewName"

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More

Rename Sheet by Name

Sheets("OldSheet").Name = "NewName"

Rename Sheet by Sheet Index Number

Here we use 1 to rename the first Sheet in the Workbook.

Sheets(1).Name = "NewName"

Rename Sheet by Code Name

This code will rename a sheet using it’s VBA code name (discussed above):

Component.Name = "NewName"

VBA Programming | Code Generator does work for you!

Check if Sheet Name Exists

We created a function to test if a Sheet with a particular name already exists.

MsgBox Sheets(1).Name

The function will return TRUE if the Sheet exists, or FALSE if it does not.

Use the function like so:

MsgBox Sheets(1).Name

Copy Sheet and Rename

This example is from our article on Copying Sheets.

After copying and pasting a Sheet, the newly created sheet becomes the ActiveSheet. So to rename a copied Sheet, simply use ActiveSheet.Name: