VBA Code - Rename Sheets

See it in action

Why you should use?

Suppose you have a list of 20 sheets and you want to organize it in a proper format. Renaming each sheet manually can be time-consuming and prone to errors. By using a VBA macro, you can automate this task, ensuring consistency and saving valuable time. This method is especially useful for large workbooks with numerous sheets that need to be renamed according to a predefined list. The VBA macro will handle the renaming process in seconds, allowing you to focus on more important tasks.

Here is the instance for your better understanding! Let's say you have a table with sheet names (using the VBA code to list the names refer VBA Code - List All Sheets) and new names that need to be changed. If you do this manually, it might take you around 1-2 minutes (based on the example). However, by using the VBA code provided below, this task will be completed in a fraction of a second.

Impact of VBA Code

Task - Renaming Multiple Sheets

VBA Code

Below is the VBA macro to rename sheets:

Sub M31_RenameSheets()
    Dim ws As Worksheet
    Dim nameSheet As Worksheet
    Dim oldName As String
    Dim newName As String
    Dim i As Integer
    Dim found As Boolean
 
    ' Set the sheet where the old and new names are stored
    Set nameSheet = ActiveWorkbook.ActiveSheet ' Change this to your actual sheet name
 
    ' Loop through the sheets
    For Each ws In ActiveWorkbook.Sheets
        ' Start from the first row (change as needed)
        i = 1
        found = False
        ' Loop through the names in the active sheet
        Do While nameSheet.Cells(i, 1).Value <> ""
            oldName = nameSheet.Cells(i, 1).Value
            newName = nameSheet.Cells(i, 2).Value
 
            ' Check if the current sheet name matches the old name
            If ws.Name = oldName Then
                ' Rename the sheet
                ws.Name = newName
                found = True
                Exit Do
            End If
            i = i + 1
        Loop
        If Not found Then
            Debug.Print "Sheet not found in list: " & ws.Name
        End If
    Next ws
 
    MsgBox "Sheets renamed successfully!"
End Sub

Usage Instructions

1. Open Excel and press Alt + F11 to open the VBA Editor.
2. Insert a new module (Insert > Module).
3. Copy and paste the above VBA code.
4. Create a sheet with a list of old and new sheet names in Column A and Column B.
5. Run M31_RenameSheets macro.
6. Your sheets will be renamed automatically!