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.