EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Information Functions VBA Name Worksheet
 

VBA Name Worksheet

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Line of Code

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.

You can download this VBA Name Worksheet Excel Template here – VBA Name Worksheet Excel Template

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.

Insert Module

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

VBA Name Worksheet Example 1-1

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

VBA Name Worksheet Example 1-2

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

VBA Name Worksheet Example 1-3

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

New Sheet Example 1-4

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

VBA Name Worksheet Example 1-5

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

VBA Name Worksheet Example 1-6

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

VBA Name Worksheet Example 1-7

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

VBA Name Worksheet Example 2-1

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

Add function Example 2-2

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

VBA Name Worksheet Example 2-3

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.

New Sheet1 Example 2-4

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

VBA Name Worksheet Example 3-1

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

Code:

Sub VBA_NameWS3()

For A = 1 To ThisWorkbook.Sheets.Count

End Sub

For loop Example 3-2

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

WorkBook sheet Example 3-3

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

VBA Name Worksheet Example 3-4

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

VBA Name Worksheet Example 3-5

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.

VBA Name Worksheet 3-6

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Name Worksheet Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW