How to Create Tabs Automatically in Excel (3 Cases)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel, sometimes you need to create new tabs based on various criteria. Also, various companies or institutions need separate Excel tabs for their employees. Opening tabs one by one and naming them is very tedious and time-consuming.


Watch Video – Create Tabs Automatically in Excel


In this Excel tutorial, you will learn how to create tabs automatically in Excel using VBA codes.

In the GIF below, cell C6:C8 contains employee names, and tabs named on this cell range are created automatically using a VBA macro in Excel.

Create Tabs Automatically in Excel


3 Cases of Creating Tabs Automatically in Excel

In Excel, you can create tabs from a single cell value or values in a cell range using VBA code. Also, you can give a custom name while creating new tabs automatically using VBA.

Here are 3 different cases of creating tabs automatically in Excel:

Creating Tab from Single Cell Value

You can create a tab from a cell value using VBA in Excel. Using the code mentioned below, you can automate this process. However, you need to know the cell’s position based on which value you want to create the tab. Also, this method has one drawback: you can not use it for multiple cell values.

Before creating tabs with VBA, check whether your Excel shows the Developer tab. If your Excel does not show the Developer tab, enable it from the ribbon.

Follow the steps below to create a tab from cell value automatically:

  1. Go to the Developer tab > Visual Basic from Code group.Selecting Visual Basic
  2. Select Module from the Insert tab in the VBA window.Selecting Module from Insert Tab
  3. Paste the following VBA code into the Module.
    'Set the function name
    Sub From_Specified_Cell_Value()
    'Activate the sheet on which the cells with values are present
    Worksheets("Cell Value").Activate
    'Addressing the specific cell after which the new tab will be named
    Sheets.Add.Name = Cells(8, 3).Value
    'Change the cell position to create a tab with another cell value
    End Sub
  4. Save the code.
  5. Press the play button or F5 to run the code.Pressing Run Button to run the code

Finally, the tab is created and named after the specific cell value from the code.

Tab created containing name in the specified cell value

Note
In the above code, you can see the specific cell address is (8,3). It indicates the cell at the intersection of the eighth row and the third column. The cell value of this cell is “Bonnie”. So the created tab’s name will be “Bonnie”. Change the cell position in your code while working if you want to create a tab with a different cell value.

Creating Tabs from Cell Range

If you need to create multiple tabs at once from the cell values of a certain cell range, you can use a VBA code. The VBA will create an input dialogue box through VBA coding and then specify the cell range in the input box, making the process automated. The advantage of this code is that you can create tabs from any cell range according to your working purpose. Also, this method is helpful if you need to create a tab from a single cell value.

To create tabs automatically from the cell range, follow the steps below:

  1. Go to the Developer tab > Code group > Visual Basic.Selecting Visual Basic
  2. In the VBA window, select Module from the Insert tab.Selecting Module from Insert Tab
  3. Paste the following VBA code into the Module.
    'Set the function name
    Sub From_Cell_Range()
    'Declare the variables
    Dim rng As Range
    Dim Cell As Range
    'Permit error handling
    On Error GoTo Errorhandling
    'Showing the input box and giving cell range as input
    Set rng = Application.InputBox(Prompt:="Choose Cell Range:", _
    Title:="Insert Cell Range", _
    Default:=Selection.Address, Type:=8)
    'For loop through cells in a selected cell range
    For Each Cell In rng
    'If the cell is not empty then the new tab name will be the cell value name
        If Cell <> "" Then
            Sheets.Add.Name = Cell
        ' Stop the If function
        End If
    'Go on with the next cell in cell range
    Next Cell
    'Jump here if an error occurs
    Errorhandling:
    End Sub
  4. Save the code into the module.
  5. Press the play button or F5 to run it.Pressing Run Button
  6. Insert cell range to create tabs in the input box.
  7. Click OK.Inserting Cell Range in Input Box

Finally, the tabs are created automatically after the cell values of the selected cell range.

Tabs created with the cell values of selected cell range


Creating Tabs from Inserted Names

In this method, you can create the tabs automatically and name them in the dialogue box after running the code. You can use this method when creating a new tab automatically with a name that is not available in your dataset. Using the VBA code, you can create a tab with a custom name instead of going through the steps of creating and naming tabs.

Follow the steps below to create tabs from inserted names automatically:

  1. Go to the Developer tab > Visual Basic from Code group.Selecting Visual Basic
  2. Select Module from the Insert tab in the VBA window.Selecting Module from Insert Tab
  3. Paste the following VBA code into the Module.
    'Set the function name
    Sub From_Inserted_Tab_Name()
    'Declaring Variables
    Dim tab_name As String
    Dim sheet As Object
    'Adding statement to ignore any error
    On Error Resume Next
    'Using InputBox to give the name of the new tab
    tab_name = InputBox("Enter the Tab Name", _
    "Insert Tab Name")
    'If the present tab has no name then the code will not work
    If tab_name = "" Then Exit Sub
    'Naming the newly created tab
    Sheets.Add.Name = tab_name
    End Sub
  4. Save the code.
  5. Press the play button or F5 to run the code.Run the code
  6. Insert the name for the tab in the input box.
  7. Click OK.Inserting Tab Name

Finally, you will see a tab created with the inserted name.

Tab Created from Inserted Name

Read More: How to Create Tabs Within Tabs in Excel


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Conclusion

In this article, you learned 3 cases of creating tabs automatically in Excel. You learned to use 3 different VBA codes to create tabs from a single cell value and cell range and inserted custom names. According to your workflow, you can use one of these 3 codes in your dataset to make your work efficient. Change the cell position or name in the VBA code while using the codes in your worksheet. I hope the methods mentioned above fulfill your quest. Comment if you have further queries or have anything to add.


Frequently Asked Question

How do I create a daily tab in Excel?

To create a daily tab in Excel:

  1. Insert the days’ input in a sheet and select the cell range.
  2. Insert a VBA code that creates tabs from the cell range automatically.
  3. Save and run the code.

How do I create a new sheet in Excel with the same format?

To create a new sheet with the same formatting as an existing worksheet:

  1. Go to the worksheet containing the format.
  2. Right-click on the sheet tab.
  3. Select the Move or Copy option.
  4. Tick the “Create a copy” checkbox in the Move and Copy dialog box.
  5. Finally, click OK.

How to troubleshoot if automatic tab creation isn’t working as expected?

Check the VBA code for errors, review the logic for tab creation conditions, and ensure that your Excel workbook is not protected. You can also use the VBA editor’s debugging tools to identify and fix issues.


Related Articles


<< Go Back to Sheets Tab in Excel | Excel Parts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

2 Comments
  1. I copied and pasted the example for method one and it only created a tab for the first cell in the set. I tried several ranges but always only got one tab. Any idea what could be happening, none of the cells were blank?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo