EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Information Functions VBA Name Worksheet
Secondary Sidebar
VBA Information Functions
  • VBA Information
    • VBA Rename Sheet
    • VBA Worksheets
    • VBA IsEmpty
    • VBA Active Cell
    • VBA Workbook
    • VBA ISNULL
    • VBA Activate Sheet
    • VBA Cells
    • VBA Workbook Open
    • VBA Protect Sheet
    • VBA Unprotect Sheet
    • VBA Delete Sheet
    • VBA Editor
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Environ

VBA Name Worksheet

By Ashwani JaiswalAshwani Jaiswal

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.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (85,982 ratings)

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
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more