How to rename multiple worksheets in Excel?

Normally to rename worksheets in Excel, we can quickly double click the sheet tab, or right click on the sheet tab to choose Rename command for renaming worksheets. That’s quite handy to rename one or two worksheets in Excel, but if we want to rename multiple worksheets within one operation, how can we do?

Using Rename command to rename worksheets

Using a handy tool to rename multiple worksheets comfortably

Using VBA code for renaming multiple worksheets

Using Rename command to rename worksheets

We can quickly rename worksheets in Excel with the Rename command according to the following procedures:

Right click on the sheet tab you want to rename, and choose Rename command from the Right-click menu. Or double click on the sheet tab to rename the worksheet. Then type a new name, and then press Enter key to rename it, see screenshot:

With this Rename command, you can rename only one worksheet at a time, for the purpose of renaming multiple worksheets, please repeat the above operations.

Using a handy tool to rename multiple worksheets comfortably

Using VBA code for renaming multiple worksheets

Here I will introduce you two VBA codes to rename multiple worksheets.

Code 1. VBA code to rename multiple worksheets by the name you want at once

Using the following VBA code, you can quickly rename all worksheets of the current workbook with the same prefix in their worksheet names, such as: KTE-order1, KTE-order 2, and KTE-order 3…

1. Click on Developer > Visual Basic, and click Insert > Module in the Microsoft Visual Basic Application Windows.

2. Please copy and paste the following code into the Module.

VBA: Rename all sheets by entering a specific name

Sub ChangeWorkSheetName()
'Updateby20140624
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
newName = Application.InputBox("Name", xTitleId, "", Type:=2)
For i = 1 To Application.Sheets.Count
    Application.Sheets(i).Name = newName & i
Next
End Sub

3.  Click  button to execute the code, and enter the name you want into the pop-out dialog.see screenshots:

4. Click OK. Then you can see all sheets are renamed.

2. VBA code to rename multiple worksheets by specific cell value in each worksheet of the active workbook

Using the following VBA code, it will rename all worksheets of current workbook by using the content of specific cell. For example, you can type the worksheet name in the A1 cell of the whole workbook, and then the worksheet will be renamed as the cell value A1.

1.  Please specify a cell to contain the worksheet name in each worksheet and type the worksheet name in it. In this example, I will type the worksheet name in cell A1 in each worksheet.

2.  Click Developer > Visual Basic, and click Insert > Module in the Microsoft Visual Basic Application Windows.

3.  Please copy and paste the following code into the Module.

VBA: Rename worksheets by a specific cell content

Sub RenameTabs()
'Updateby20140624
 For x = 1 To Sheets.Count
 If Worksheets(x).Range("A1").Value <> "" Then
 Sheets(x).Name = Worksheets(x).Range("A1").Value
 End If
 Next
 End Sub

4.  Click  button to execute the code. All worsheets are renamed based on the cell content of A1.

Demo: Rename multiple worksheets with Kutools for Excel

Related articles