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
Good for Excel starters to rename one or two worksheets

Using VBA code for renaming multiple worksheets
Good for Excel advanced user to rename multiple worksheets, but it’s complicated

Using a handy tool to rename multiple worksheets comfortably
Good for all Excel users to quickly rename multiple worksheets, too easy!

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Using Rename command to rename worksheets

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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

Step 1: 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.

doc-rename-worksheets1-2doc-rename-worksheets2

Step 2: Then type the new worksheet name.

Step 3: Press Enter Key to rename it. See screenshot:

doc-rename-worksheets3

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.


arrow blue right bubble Using VBA code for renaming multiple worksheets

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

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…

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

Step 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

Step 3: click doc-rename-multiple-worksheets-4 button to execute the code, and enter the name you want into the pop-out dialog.see screenshots:

doc-rename-worksheets-1

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

doc-rename-worksheets4
-1
doc-rename-worksheets5

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.

Step 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 every worksheet.

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

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

VBA: Rename worksheets by cell contents.

Sub RenameTabs()
'Updateby20140624
xTitleId = "KutoolsforExcel"
xInput = Application.InputBox("Name", xTitleId, "", Type:=2)
For i = 1 To Application.Sheets.Count
Application.Sheets(i).Name = xInput & i
Next
End Sub

Step 4: Click doc-rename-multiple-worksheets-4 button to execute the code. A dialog pops out for you to select a range to rename all the worksheets.

doc-rename-worksheets-2

Step 5: Then click OK. All worsheets are renamed based on the range.

Note: 1. If the specific cell has no content, then the worksheet of the specific cell will be named as number.


arrow blue right bubble Using a handy tool to rename multiple worksheets comfortably

The Rename Multiple Worksheets tool of Kutools for Excel is quite handy for renaming all worksheets or selected specific worksheets of current workbook.

With this tool, you can quickly rename all worksheets or selected specific worksheets by adding extra content before or after the existing worksheet name or replace the original sheet names with the new names. You can also rename all worksheets or selected worksheets by using the contents of a range cells.

1. Rename multiple worksheets with specific data by using Kutools for Excel

With this Rename Multiple Worksheets tool, you can easily rename the worksheets with a specific value which you can put it before or after the original worksheets name, also you can replace the original with the specific value. Do as follows:

Step 1: Click Enterprise > Worksheet Tools > Rename Multiple Worksheets. See screenshot:

doc-rename-worksheets6

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

doc-rename-worksheets7


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.

Step 3: And then click OK. You will get the following results:

doc-rename-worksheets8
-1
Insert the specific value before original sheet name
doc-rename-worksheets9
Insert the specific value after original sheet name
doc-rename-worksheets10
Replace the original sheet name with the specific value
doc-rename-worksheets11

2. Rename multiple worksheets with cell values by using Kutools for Excel

If you have a range of cell values, and now you need to rename the worksheet name with the cell values, this utility also can help you quickly rename multiple worksheets with the specific cell values. Please do as this:

Step 1: Select the range of cell values that you want to use.

Step 2: Then apply this utility. And in the Rename Multiple Worksheets dialog box, specify the following settings:

Select the worksheets you want to rename from the Worksheets list.
select one type which you want to rename the worksheets under Rename Options.
doc-rename-worksheets12

Step 3: And then click OK. You will get the following results:

Insert the cell value before original sheet name
doc-rename-worksheets13
Insert the cell value after original sheet name
doc-rename-worksheets14
Replace the original sheet name with the cell value
doc-rename-worksheets15

Kutools for excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

For more detailed information about Rename multiple worksheets, please visit Rename multiple worksheets feature description.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

 

Add comment


Security code
Refresh