How to Change or Rename the Sheet Name in Excel VBA

 

In this tutorial, we will learn about how to change or rename a sheet name in Excel VBA.

Here we can see that, there are four sheets are appearing in the below image.

In VBA, we have three methods to change or rename any of the sheets within a workbook.

First Method: Active Sheet Method

Using this method, we can change the name of an active sheet.

What is the Active Sheet?

Suppose that we are working in “Sheet1”, so in this case, our the active sheet is “Sheet1”.

Now with the help of VBA macro, we will change the active sheet name.

See the below VBA macro:-

Sub Change_Current_Sheet_Name()

ActiveSheet.Name = "Main_Dataset"

End Sub

Write the above code and run by pressing F8 for step by step execution.

This is our first method to rename or change the sheet name by VBA macro.

Using the Sheet Index or Number Method

In this method, we will rename or change the Sheet by its index or number.

Suppose that we want to change our sheet number 3, where Sheet3 name as Old_Data.

See the below VBA macro

Sub Rename_Sheet_By_IndexNumber()

Sheets(3).Name = "Old_Data"

End Sub

See Image Below:-

Third Method for Renaming Sheets by Using Sheet Name 

In this method, we will use a sheet name to change or rename sheets by VBA macro.

Now suppose that we want to change the name of Sheet4 with New_Data by VBA macro.

Use the below VBA code:-

Sub Rename_Sheet_By_SheetName()

Sheets("Sheet4").Name = "New_Data"

End Sub

So, these are the three methods to change or rename sheet names by VBA macros.

In this tutorial, you learned about how to change or rename sheets name in Excel VBA.

I hope you find this tutorial useful and helpful.

Please feel free to put your comments or suggestion in the below box.

Thanks

Narendra

Related Posts

How to Insert a New Sheet In Excel VBA

What is Current Region Property in VBA

How to use Orientation in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

How to Change Font Color in Excel VBA