If you have lots of worksheets needed to be renamed now, how will you do? Rename them manually one by one with “Rename” command in Excel or automatically with VBA code or other programs? Just see three ways talked about in this post, you will have your best choice.

worksheet needed to be renamed

Way 1: Rename multiple worksheets manually with “Rename” command

This is the most common way that everyone should know to rename worksheet tab in Excel. We can give a totally different name for one worksheet from another. But we have to do this manually one by one.

1. Open the excel workbook that includes worksheets you need to rename.

2. Right click the sheet tab and select “Rename” option.

rename worksheet with rename command

3. Enter a new name for the worksheet and press Ctrl + S to save changes to workbook.

enter new name for worksheet one by one

Way 2: Rename multiple worksheets at once with VBA code

This is general but a little difficult way to automatically rename multiple worksheets by the same at once.

Step 1: Press Alt + F11 to open Microsoft Visual Basic for Applications and insert a module.

run VBA and enter VBA code

Step 2: Copy and paste following codes into the Module.

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

Step 3: Press F5 to run the VBA code and enter the name you want in the pop-up dialog.

confirm to run code to rename multiple worksheets

enter same part in new worksheet name

Step 4: Click OK and you can see all the sheets are renamed with characters you typed.

successfully rename multiple worksheets with VBA code

 

Way 3: Rename multiple worksheets easily with Kutools for Excel

It is one of the most effective ways I know that can easily rename multiple worksheets fast in few seconds. But at first, you should get Kutools for Excel and install it.

Step 1: Click Enterprise > Worksheet > Rename Multiple Worksheets.

choose to rename multiple worksheets with Kutools for Excel

Step 2: Specify the settings in Rename Multiple Worksheets dialog box.

  1. Select the worksheets you want to rename from the Worksheets list.
  2. Input the specific value into the “From an input box“.
  3. Select one type which you want to rename the worksheets under Rename Options.

specify worksheet new name settings

Step 3: Click OK to rename selected worksheets with specified settings.

successfully rename multiple worksheets with Kutools for Excel

Related Articles: