Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 VBA code for renaming multiple worksheets

Using a handy tool to rename multiple worksheets comfortably


Easily rename multiple worsheets with specified text, cell values or specific cell value in each sheet:

The Rename Multiple Worksheets utility of Kutools for Excel can help you easily rename multiple worksheets at the same time in Excel. You can apply this utility to:

  • Rename multiple worksheets with specific data;
  • Rename multiple worksheets with specific cell values;
  • Rename multiple worksheets with a specific cell value in each worksheet. See screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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 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…

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 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 2

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

doc rename worksheets 3

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 doc-rename-multiple-worksheets-4 button to execute the code. All worsheets are renamed based on the cell content of A1.

Notes:

1. In the above code, A1 is the cell content which you want to rename the worksheets based on, you can change it to your need

2. If the specific cell has no content, then the worksheet of the specific cell will not be renamed.


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:

1.  Click Kutools Plus > Worksheet > Rename Worksheets. See screenshot:

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

doc rename worksheets 5

1. Select the worksheets you want to rename from the Worksheets list.

2. Select one type which you want to rename the worksheets under Rename Options.

3. Input the specific value into the From an input box.

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

doc rename worksheets 6

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:

1. Apply this utility by clicking Enterprise > Worksheet > Rename Multiple Worksheets . And in the Rename Multiple Worksheets dialog box, specify the following settings:

doc rename worksheets 7

1. Select the worksheets you want to rename from the Worksheets list.

2. Select one type which you want to rename the worksheets under Rename Options.

3. Click  doc button button to select the cell values that you want to name the worksheets based on under the From specific range section.

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

doc rename worksheets 8

3. Rename multiple worksheets with a specific cell value in each worksheet by using Kutools for Excel

To rename the worksheet with a specific cell value in each worksheet, Kutools for Excel's Rename Multiple Worksheets also can do you a favor, please do as follows:

1. Apply this utility by clicking Enterprise > Worksheet > Rename Multiple Worksheets . And in the Rename Multiple Worksheets dialog box, specify the following settings:

doc rename worksheets 9

1. Select the worksheets you want to rename from the Worksheets list.

2. Select one type which you want to rename the worksheets under Rename Options.

3. Click  doc button button to select the specific cell value that you want to name the worksheets based on under the Rename worksheets with specific cell section.

2. Then click Ok button, the worksheet names have been renamed with the specific cell value in each worksheet, see screenshot:

doc rename worksheets 10

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel


Demo: Rename multiple worksheets with Kutools for Excel


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    krishnadevan · 7 months ago
    i wants some requirments
    1: i wants two or more blank sheets between two main sheets.

    2: i want to copy one condent from sheet 1 to other sheets, at the same time the heading condet sheet name wil be chenged both sheets. that

    sheet 01 heading is sheet 01. icopy the condent and paste it to other 1500 sheet at the same time i want to change the sheet name will be changed sheet 02, sheet 03 this orderly

    3: i want to copy one condent from first sheet and i want to paste it some specific pages..
  • To post as a guest, your comment is unpublished.
    jhs3fh · 1 years ago
    This is fab, I've been happily using '2. VBA code to rename multiple worksheets by specific cell value in each worksheet of the active workbook for months but now work have upgraded to Excel 2010 and it doesn't work anymore... can't find a similar solution online - any suggestions?
  • To post as a guest, your comment is unpublished.
    Murat · 1 years ago
    Thats great!!!, thank you very much for sharing vba codes. they are very useful
  • To post as a guest, your comment is unpublished.
    Murat · 1 years ago
    Thats great!!!, thank you very much for sharing vba codes. they are very useful
  • To post as a guest, your comment is unpublished.
    sylvie · 2 years ago
    What if we want to have the numbers in descending order from 52 to 1?
  • To post as a guest, your comment is unpublished.
    Jennifer · 3 years ago
    I need to rename worksheets with data in cell Q5, however, multiple worksheets contain the same data in Q5. How can I tell Excel to rename those worksheets with the data in Q5 and just add something (like 1 or 2) after the name? I hope this makes sense.
  • To post as a guest, your comment is unpublished.
    Jonna · 3 years ago
    I have tried to come up with something very similar, I have worksheet that has 2 columns and 119 rows. I need to take A1:B1 put them together and rename 3rd worksheet, next A2:B2, put them together and rename 4th worksheet, on until row 119. I have been hitting my head on the monitor trying to come up with easy way to do this. Any help would be much appreciated!
  • To post as a guest, your comment is unpublished.
    Jonna · 3 years ago
    I have been looking for something very similar to these, however...I have sheet that has 2 columns and 119 rows. I need to combine A1:B1, start at the 3rd worksheet to rename the tab, next would be A2:B2, rename the 4th tab, so on and so forth. I have been beating my head against monitor and can't figure this one out...any help would be appreciate it.
  • To post as a guest, your comment is unpublished.
    Andy · 3 years ago
    Awesome. saved me so much time thanks
  • To post as a guest, your comment is unpublished.
    Jeremy · 3 years ago
    Thank you so much! Best Macro ever to rename the worksheets after cells. I have been searching for a way to do this for months, and you are a life-saver!
  • To post as a guest, your comment is unpublished.
    Caroline Schroder · 4 years ago
    Here's a cool variation a colleague and I put together to rename multiple worksheets while skipping over others. Just hide those sheets you don't want to change:

    Sub ChangeWorkSheetName()
    'Updateby20140624
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    newName = Application.InputBox("Name", xTitleId, "", Type:=2)
    j = 1
    For i = 1 To Application.Sheets.Count
    If Application.Sheets(i).Visible Then
    Application.Sheets(i).Name = newName & j
    j = j + 1
    End If
    Next
    End Sub
  • To post as a guest, your comment is unpublished.
    marymallay · 4 years ago
    This works well ! HOw can I modify the code to start only at the second page?

    Thank you!
  • To post as a guest, your comment is unpublished.
    HA · 5 years ago
    I am looking for a VBA code to rename 10-12 sheets names and would like to mention the names inside the code as opposed to typing it in the prompt window. Can you please help me in that.

    Thanks
    -H
  • To post as a guest, your comment is unpublished.
    tosin ibadu · 5 years ago
    Hi please wat if my excel sheet has notin like developer or kutools or enterprise? I use excel 2010 on windows 8.pls help!
  • To post as a guest, your comment is unpublished.
    loushy · 5 years ago
    Do you know how much time you've saved me? You rock!
  • To post as a guest, your comment is unpublished.
    Pinky · 5 years ago
    Thanks sooo much ,, it helps me a lot :-)
  • To post as a guest, your comment is unpublished.
    william · 6 years ago
    What if we dont download kutools, can we still name sheets by a range of cells?
  • To post as a guest, your comment is unpublished.
    random guy · 6 years ago
    thanks.This is helpful :-)
  • To post as a guest, your comment is unpublished.
    Jay Chivo · 6 years ago
    [quote name="random guy"]what if instead of kteorder1 ,we need to display 1-12-13,2-12-13 etc(where to place the "i" value?[/quote]
    Hello, do you want to rename worksheets name as 1-12-13,2-12-13?

    Please type in the worksheet names into a range first, and then you can use them for renaming worksheets as:

    [img]http://www.extendoffice.com/images/stories/doc-comments/rename-worksheets.png[/img]
  • To post as a guest, your comment is unpublished.
    random guy · 6 years ago
    what if instead of kteorder1 ,we need to display 1-12-13,2-12-13 etc(where to place the "i" value?