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
- First, define the sheet that you want to rename using the worksheet object.
- After that, you need to use (.Name) to access the name property that you want to change.
- Next, type an equal sign to that to tell VBA the value you want to use to rename the sheet.
- 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.NameGet Sheet Name by index Number
This will display the first worksheet name in a message box:
MsgBox Sheets(1).NameThis will display the name of the last worksheet in the workbook:
MsgBox Sheets(Sheets.Count).NameGet 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").Activateor the VBA code name:
CodeName.ActivateReferencing 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.NameRename 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).Name0
The function will return TRUE if the Sheet exists, or FALSE if it does not.
Use the function like so:
MsgBox Sheets(1).Name1
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: