VBA Name Worksheet

Excel VBA Name Worksheet

This is one of the easiest tasks to do. Changing the worksheet name in VBA can be done manually and automatically and both the ways are easy. Sometimes we may need to change the worksheet name just to process and continue some work. Excel VBA Name Worksheet can be the requirement of some process work where we need to change the name of Worksheet after the task is completed or just to differentiate between some worked on the sheet we could also use VBA Name Worksheet to automate this process.

There are many different ways to change the name of any worksheet. But the simplest and easiest way to do it as shown below.

Where in the above-shown line of code, NAME = Property in VBA, which is used when we want to use the worksheet name in any manner.

How to Change Name of Worksheet in Excel VBA?

We will learn how to change the name of a worksheet in Excel by using the VBA Code.

VBA Name Worksheet – Example #1

Let’s see a simple example where we will change the name of any worksheet. For this, follow the below steps:

Step 1: Open a Module from Insert menu tab firstly as shown below.

Step 2: Write the subprocedure of the VBA Name Sheet. We can choose any name to define the module VBA Code.

Code:

Sub VBA_NameWS1()

End Sub

Step 3: Define a variable for Worksheet function in any name as shown below. Better use the name which shows or represents that variable.

Code:

Sub VBA_NameWS1()

Dim NameWS As Worksheet

End Sub

Step 4: Now use that variable and set that with Worksheet name which we want to change as shown below.

Code:

Sub VBA_NameWS1()

Dim NameWS As Worksheet
Set NameWS = Worksheets("Sheet1")

End Sub

Step 5: Now use Name function with a variable which we defined and choose a new name which we want to give the selected sheet. Here, our sheet is Sheet1 and the new name is New Sheet.

Code:

Sub VBA_NameWS1()

Dim NameWS As Worksheet
Set NameWS = Worksheets("Sheet1")
NameWS.Name = "New Sheet"

End Sub

Step 6: Before we run the code, let’s just see the name of sheets down there.

Step 7: Now Run the code by clicking on the Play button located below the menu bar.

Step 8: We will see the name of the sheet will get changed to New Sheet from Sheet1.

VBA Name Worksheet – Example #2

There is another way to change the name of any worksheet using VBA. This is also as easy as shown in example-1. We add a new worksheet and change the name of that worksheet. For this, follow the below steps:

Step 1: Write the subprocedure of the VBA name worksheet in any suitable name as shown below.

Code:

Sub VBA_NameWS2()

End Sub

Step 2: To add a new worksheet, we will use the Worksheets command along with Add function.

Code:

Sub VBA_NameWS2()

Worksheets.Add

End Sub

Step 3: Now to change the name of the added worksheet, we will use the above line of code and with the help of Name function insert a new name. Here, we have considered New Sheet1 as a new name.

Code:

Sub VBA_NameWS2()

Worksheets. Add
Worksheets.Add.Name = "New Sheet1"

End Sub

Step 4: Now run the code by pressing the F5 key. We 0will see, a new worksheet will be added apart from the sheets which we have seen in example-1, in the name of New Sheet1 as shown below.

VBA Name Worksheet – Example #3

There is another way to perform this activity. In this example, we will do VBA Name Worksheet with the help of For-Next Loop. We will create a loop to see how many worksheets are there in the current workbook with their names. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Name Worksheet as shown below.

Code:

Sub VBA_NameWS3()

End Sub

Step 2: Open a For loop in which we will start the count the worksheet names from the 1^st position till the Worksheet are there in the current workbook.

Code:

Sub VBA_NameWS3()

For A = 1 To ThisWorkbook.Sheets.Count

End Sub

Step 3: Now to see the names of worksheets we will use MsgBox to carry current WorkBook sheet names as shown below.

Code:

Sub VBA_NameWS3()

For A = 1 To ThisWorkbook.Sheets.Count
MsgBox ThisWorkbook.Sheets(A).Name

End Sub

Step 4: Close the loop with Next as shown below.

Code:

Sub VBA_NameWS3()

For A = 1 To ThisWorkbook.Sheets.Count
MsgBox ThisWorkbook.Sheets(A).Name
Next

End Sub

Step 5: Before we run the code, let’s have a look at the sheet names which we have as shown below.

Step 6: Now it is expected that we would get these names in the message box, so we will run this code. We will see different message box is now carrying the names of all the sheet names that we have in sequence as shown below.

Pros and Cons of VBA Name Worksheet

• This makes easy to change the name of any worksheet when we have to automate the full process.
• We can even check the names of any or all the worksheets even though they are hidden.
• Although it is an automated way of using the worksheet names it does not give much impact on improvement unless the code size is huge.

Things to Remember

• Above shown steps can be compressed more into 1 line of code.
• Save the workbook in macro enable excel format to preserve the written VBA code.
• VBA has named as property.
• We can many types of tasks such as changing the name of the worksheet to extracting the name of the worksheet to adding a sheet and then naming it.
• If there is any mismatch in the name of the worksheet which we supply then we will end up with getting an error message as Subscript out of range.

Recommended Articles

This is a guide to VBA Name Worksheet. Here we discuss how to change the name of worksheets in Excel using VBA code along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

1. VBA Delete Sheet
2. VBA IF Statements
3. VBA Unprotect Sheet
4. VBA While Loop