EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Information Functions VBA Rename Sheet
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 Rename Sheet

By Ashwani JaiswalAshwani Jaiswal

VBA Name Sheet

Rename Sheet in Excel VBA

Renaming any excel worksheet is as easy as renaming any file. This can be done by double-clicking on any worksheet name or by selecting Rename from the right list menu list. But what if we want to rename an excel worksheet with the help of VBA macro. With the help of VBA Macro, we can rename one or more than one Excel worksheet in a single shot. We can even fix the name of any worksheet to avoid getting changed by anyone. Here, we will see, multiple ways to rename any worksheet.

How to Rename Sheet in Excel Using VBA?

Below are the different examples to Rename Sheet in Excel using VBA code.

You can download this VBA Rename Sheet Excel Template here – VBA Rename Sheet Excel Template

VBA Rename Sheet – Example #1

Although it is very easy to rename any excel worksheet with the help of VBA. There are many ways to do that as well. Here, the name of our worksheet is default set as “Sheet1” as shown below, which we will be renaming.

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 (86,192 ratings)

VBA Rename Sheet Example 1.1

Watch our Demo Courses and Videos

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

Step 1: For this example, we would need a module where we will be doing the coding. Go to Insert menu and select Module as shown below.

VBA Rename Sheet Example 1.2

Step 2: Now in the newly opened Module, write the subcategory in the name of VBA Rename Sheet or in any other name.

Code:

Sub VBA_RenameSheet()

End Sub

VBA Rename Sheet Example 1.2

Step 3: Now define a variable in any name and assign it with Worksheet datatypes as shown below. Here, we have named it as “Sheet”.

Code:

Sub VBA_RenameSheet()

Dim Sheet As Worksheet

End Sub

VBA Rename Sheet Example 1.3

Step 4: With the help of Set command, select Sheet variable as shown below.

Code:

Sub VBA_RenameSheet()

Dim Sheet As Worksheet
Set Sheet =

End Sub

Example 1.4

Step 5: And select the Worksheet which we need to rename with the help of Worksheet command.

Code:

Sub VBA_RenameSheet()

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

End Sub

Example 1.5

Step 6: Now use Sheet function along with Name, and assign the name which we want to rename. Here we are choosing “Renamed Sheet”.

Code:

Sub VBA_RenameSheet()

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

End Sub

Example 1.6

Step 7: Now, at last, compile the code and run it if there is an error found. For running the code, click on the Play button located below the menu bar as shown below. We will see, our sheet will get renamed from Sheet1 to Renamed Sheet as shown below.

VBA Rename Sheet Example 1.8

VBA Rename Sheet – Example #2

In this example, we will see a much easier way to rename any worksheet with the help of VBA macro. For this, we would need, another fresh module.

Step 1: Open a module and write the subcategory of VBA Rename as shown below.

Code:

Sub VBA_RenameSheet1()

End Sub

VBA Rename Sheet Example 2.1

Step 2: Select the Sheet which we want to rename with Select function. Here our sheet is Sheet1.

Code:

Sub VBA_RenameSheet1()

Sheets("Sheet1").Select

End Sub

VBA Rename Sheet Example 2.2

Step 3: In the next line, consider the Name function with the selected sheet as shown below.

Code:

Sub VBA_RenameSheet1()

Sheets("Sheet1").Select
Sheets("Sheet1").Name =

End Sub

VBA Rename Sheet Example 2.3

Step 4: Choose the name which we want to give after renaming the sheet. We are choosing the same name as did in example-1 as “Renamed Sheet”

Sub VBA_RenameSheet1()

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Renamed Sheet"

End Sub

Example 2.5

Step 5: Again compile the code and run it. We will see the sheet1 will get Renamed with a new name as “Renamed Sheet”.

Example 2.5

VBA Rename Sheet – Example #3

There is again one more similar way to rename an excel worksheet with the help of VBA. This would look a little similar to the process which we have seen in example-2, but much easier to remember and process.

Step 1: Open a module and write the new subcategory of VBA Rename Sheet as shown below.

Code:

Sub VBA_RenameSheet2()

End Sub

VBA Rename Sheet Example 3.1

Step 2: Select the sheet which we want to rename. Here we can select the sheet by putting the sequence of Sheet into brackets as shown below. So we don’t have to write the name of Sheet to be renamed. Only sequence of that sheet would work.

Code:

Sub VBA_RenameSheet2()

Sheets(1).Select

End Sub

VBA Rename Sheet Example 3.2

Step 3: In the next line, consider the Name function with the selected sheet sequence as shown below.

Code:

Sub VBA_RenameSheet2()

Sheets(1).Select
Sheets(1).Name =

End Sub

VBA Rename Sheet Example 3.3

Step 4: Now enter the name which we want to give to selected Sheet1. Let’s consider the name which we have seen in the above examples as “renamed Sheet”.

Code:

Sub VBA_RenameSheet2()

Sheets(1).Select
Sheets(1).Name = "renamed Sheet"

End Sub

Example 3.4

Step 5: Now compile the code again and run. We will notice that Sheet1 is now again renamed to “renamed Sheet” as shown below.

 Example 3.5

VBA Rename Sheet – Example #4

In this example, we will see how to rename any worksheet with just one line of code.

Step 1: Open a module and write the subcategory of VBA Rename Sheet as shown below.

Code:

Sub VBA_RenameSheet3()

End Sub

VBA Rename Sheet Example 4.1

Step 2: Select the sheet sequence which we want to rename along with Name function and give a name by which we want to rename that sheet as shown below.

Code:

Sub VBA_RenameSheet3()

Sheets(1).Name = "rename Sheet"

End Sub

VBA Rename Sheet Example 4.2

Step 3: Now simply run the code to implement it. We will again observe that Sheet1 is now renamed as “rename Sheet”

Example 4.3

Pros of Excel VBA Rename Sheet

  • If we have multiple sheets to be renamed then using VBA code is much better than the manual process.
  • We can change the name of any excel worksheet by one line VBA code which we have seen in example-4.
  • Although manually changing the name of any sheet is also a simple way of doing it. But VBA Rename Sheet is quite helpful when we are creating a new sheet and we want to separate that with other sheets just to categorize it.

Things to Remember

  • To get a better view on changes happening, keep VBA window parallel to excel sheet so that changing to sheet name will be visible clearly.
  • Saving the file after work is done in the Macro enable format will allow us to preserve the code for future work as well.
  • Automating the process of changing the sheet name will help us in reducing the time categorizing the sheets and data.

Recommended Articles

This is a guide to VBA Rename Sheet. Here we discuss how to Rename Sheet in Excel using VBA code along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Workbook
  2. VBA Name Worksheet
  3. VBA Unprotect Sheet
  4. VBA Protect Sheet
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course
20 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