How To Rename Multiple Sheet In Excel Based On Cell Value

In Excel, renaming multiple sheets can be a time-consuming task if done manually. Fortunately, there is a quick and easy way to rename multiple sheets based on the cell value using VBA code.

This tutorial will guide you through the steps to create a VBA macro that renames each sheet in your workbook based on the value of a specific cell. By executing this macro, you can save yourself a lot of time and effort and streamline your workflow when working with large amounts of data in multiple sheets within Excel.

1. How To Rename Multiple Sheet In Excel Based On Cell Value?

1. You can easily rename multiple sheets in Excel based on the cell value by using a simple VBA code. Here are the steps.
2. Open the Excel workbook that contains the sheets you want to rename.
3. Click the Developer tab —> Visual Basic button in the Code group to open the Visual Basic Editor.
4. You can also use the keyboard shortcut Alt + F11 to open the Visual Basic Editor.
5. In the editor, click on Insert > Module to create a new module.
6. Paste the following VBA code into the module:

   Sub RenameSheets()
    
       ' define a worksheet varialbe.
       Dim ws As Worksheet
       
       ' pop up an alert box to show the worksheet count in the current workbook.
       MsgBox ThisWorkbook.Sheets.Count
       
       ' loop all the worksheets in the current workbook.
       For Each ws In ThisWorkbook.Sheets
       
          ' rename the current worksheet with the first cell value in the current worksheet.
           ws.Name = ws.Range("A1").Value
       
       Next ws
   
   End Sub

7. In the line “ws.Range(“A1”).Value“, replace “A1” with the cell reference of the cell that contains the name you want to use for each sheet.
8. Press F5 or click on the Run button to execute the macro.
9. All sheets in the workbook will be renamed based on the value in the specified cell.
10. Note1: Make sure to save a backup copy of your workbook before running the macro in case you encounter an error in the code.
11. Note2: To run this example successfully, you need to input text value in cell A1 of each worksheets. Otherwise, you may encounter the error like Run-time error ‘1004’: Method ‘Name’ of object’_Worksheet’ failed.
12. You can change the module’s source code to below to avoid the error.

    Sub RenameSheets()
     
        ' define a worksheet varialbe.
        Dim ws As Worksheet
        
        ' pop up an alert box to show the worksheet count in the current workbook.
        MsgBox ThisWorkbook.Sheets.Count
        
        ' loop all the worksheets in the current workbook.
        For Each ws In ThisWorkbook.Sheets
        
           ' rename the current worksheet with the first cell value in the current worksheet.
           cellValue = ws.Range("A1").Value
           
           ' if cell A1's value is not null and is not empty.
           If (IsNull(cellValue) = False And IsEmpty(cellValue) = False) Then
           
               ws.Name = cellValue
               
           End If
        
        Next ws
    
    End Sub