Rename Multiple Worksheets Using Values from the Same Cell in Excel

Workbooks that contain a separate report for each employee, customer, branch, or project often begin with generic worksheet names such as Report 01, Report 02, and Report 03. If every worksheet already contains its identifying name in the same cell, you can use those cell values to rename all the worksheet tabs instead of editing them individually.

In the example used here, the workbook contains 12 employee performance reports. Each employee name is stored in cell B3 of the corresponding worksheet. The following methods rename every report sheet with the employee name found in B3.

• Rename multiple worksheets from the same cell with VBA
• Rename multiple worksheets from the same cell with Kutools for Excel
• VBA vs. Kutools for Excel
• Frequently Asked Questions
• Conclusion

Video: Rename sheets from the same cell value in Excel

Rename multiple worksheets from the same cell with VBA

A VBA macro can go through every worksheet in the active workbook, read the value in B3, and use that value as the new worksheet name. This method is useful when you do not mind running a short macro.

1. Make sure the value you want to use as the worksheet name is stored in the same cell on every worksheet. In this example, each employee name is in cell B3.
2. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module.
4. Paste the following code into the module:

   Sub RenameWorksheetsFromSameCell()
       'Updated by Extendoffice
       Dim ws As Worksheet
       Dim newName As String
       Dim renamedCount As Long
   
       For Each ws In ActiveWorkbook.Worksheets
           newName = Trim(CStr(ws.Range("B3").Value)) 'Replace "B3" with your target cell address.
   
           If newName <> "" Then
               On Error Resume Next
               Err.Clear
               ws.Name = newName
   
               If Err.Number = 0 Then
                   renamedCount = renamedCount + 1
               End If
   
               On Error GoTo 0
           End If
       Next ws
   
       MsgBox renamedCount & " worksheet(s) renamed.", vbInformation
   End Sub

5. Place the cursor anywhere inside the code, and press F5 to run the macro.

The macro reads B3 on each worksheet. For example, "Report 01" is renamed "Olivia Carter", "Report 02" is renamed "Liam Brooks", and the remaining report sheets are renamed in the same way.

Rename multiple worksheets from the same cell with Kutools for Excel

If you frequently prepare workbooks containing many individual reports, the Rename Worksheets feature in Kutools for Excel provides a direct way to rename selected worksheets using the value from the same cell on each sheet. It does not require VBA, and you can choose exactly which worksheets to process.

1. Click Kutools Plus > Worksheet > Rename Worksheets.
2. In the Rename Multiple Worksheets dialog box, select the report worksheets you want to rename.
3. Under Rename Options, select Replace original sheet name.
4. Under New Worksheet Name, select Rename worksheets with specific cell, and specify cell B3.
5. Click OK.

   

Kutools takes the value from B3 of each selected worksheet and applies it to that worksheet tab. The 12 generic report tabs are therefore renamed with their corresponding employee names in one operation.

VBA vs. Kutools for Excel

Frequently Asked Questions

Conclusion

When every worksheet contains its identifying value in the same location, that cell provides a reliable source for batch-renaming the worksheet tabs. VBA is suitable for an occasional workbook-wide rename, while Kutools for Excel is more convenient when you regularly process report workbooks or need to select specific worksheets. Before renaming, check that the source values are unique and comply with Excel's worksheet naming rules.

We hope this tutorial helps you rename multiple worksheets more efficiently, whether you prefer the flexibility of VBA or the user-friendly options provided by Kutools for Excel. For more step-by-step guides, formulas, productivity tips, and solutions to common Excel tasks, explore our complete collection of Excel tutorials.