Posts Tagged ‘rename multiple sheets in excel vba’

4 Options to Rename One or Multiple Worksheet Tabs in Excel

January 23rd, 2017 by Admin

How can I rename multiple worksheets at one time using VBA code? In Excel, you can assign a unique name to each sheet tab in your workbook so you can easily find the tab you want. In this tutorial we’ll show you 4 simple methods to rename one or multiple worksheet tabs in Excel.

Note: The name of a worksheet tab can have a maximum of 30 characters only.

Option 1: Rename worksheet tab with the “Rename” shortcut menu

Right-click on a specific tab you want to rename. You can see many options coming up when you right click. From there, choose the Rename shortcut menu.

worksheet-rename-menu

Type a name that you want to give to the worksheet tab and press Enter. Repeat this procedure for each tab you want to rename.

type-new-name-for-worksheet

Option 2: Rename worksheet tab by double-clicking the tab

Another way to rename a worksheet tab in Excel is by double-clicking on the sheet tab. When you double-click on the worksheet tab at the bottom, the tab name (e.g. Sheet1) is highlighted. After that, you just have to type the new name.

double-click-sheet-to-rename

Option 3: Rename worksheet tab using keyboard shortcut

Select the worksheet tab that you want to rename, then press the keyboard shortcut Alt + O + H + R (Just press these 4 keys on the keyboard one by one in the sequence), type a name and press Enter.

Option 4: Rename multiple worksheet tabs using VBA code

It could be a time-consuming task if you use the above methods to rename lots of worksheet tabs. To simplify the job, you can rename multiple worksheet tabs using VBA code.

  1. After opening your Excel workbook, press the Alt + F11 shortcut to open Visual Basic Editor. Right-click on VBAProject on the left pane and choose Insert -> Module to insert a new module.
  2. Copy the following VBA code and paste it to the right pane of the VBA editor (“Module1” window). Click the Run menu, and select Run Macro.

    Sub RenamingSheets()
    nmbr = InputBox("What's the first number you want to name the sheets?", "Renaming Sheets")
    For ws = 1 To Worksheets.Count
    Sheets(ws).Name = "KB" & nmbr
    nmbr = nmbr + 1
    Next ws
    End Sub

    rename-sheet-with-vba

  3. All your worksheet tabs will be quickly renamed with new names like “KB1”. You can change the VBA code to rename worksheet to what you want.

    multiple-sheets-renamed