Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Rename Worksheets Based on Cell Values in Excel?
You may come across circumstances where renaming worksheets manually becomes time-consuming and error-prone if you routinely work with large Excel spreadsheets with numerous sheets. Thanks to macros and Visual Basic for Applications (VBA), Excel thankfully offers a robust solution that enables you to automate this procedure.
This tutorial will show you how to create a straightforward VBA script that renames worksheets according to particular cell values found in each sheet. These tips will help you manage Excel workbooks with several sheets while saving you important time and reducing the possibility of mistakes.
Rename Worksheets Based on Cell Values
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can rename worksheets based on cell values in Excel.
Step 1
Consider an Excel workbook.
First, select the cell containing the name, then right-click on the sheet name and select View Code to open the VBA application.
Select Cell > Right Click > View Code.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Code
Sub RenameSheet()
Dim xWs As Worksheet
Dim xRngAddress As String
Dim xName As String
Dim xSSh As Worksheet
Dim xInt As Integer
xRngAddress = Application.ActiveCell.Address
On Error Resume Next
Application.ScreenUpdating = False
For Each xWs In Application.ActiveWorkbook.Sheets
xName = xWs.Range(xRngAddress).Value
If xName <> "" Then
xInt = 0
Set xSSh = Nothing
Set xSSh = Worksheets(xName)
While Not (xSSh Is Nothing)
Set xSSh = Nothing
Set xSSh = Worksheets(xName & "(" & xInt & ")")
xInt = xInt + 1
Wend
If xInt = 0 Then
xWs.Name = xName
Else
If xWs.Name <> xName Then
xWs.Name = xName & "(" & xInt & ")"
End If
End If
End If
Next
Application.ScreenUpdating = True
End Sub
Step 3
Then click F5 to run the module. Then you will see that all the sheets will be renamed based on a cell value.
This is how you can rename worksheets based on cell values in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can rename worksheets based on cell values in Excel to highlight a particular set of data.
125 Views
