How to Create Tabs Automatically in Excel (3 Cases)

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.

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.

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.
2. Select Module from the Insert tab in the VBA window.
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.

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

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.
2. In the VBA window, select Module from the 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.
6. Insert cell range to create tabs in the input box.
7. Click OK.

Finally, the tabs are created automatically after the cell values of the 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.
2. Select Module from the Insert tab in the VBA window.
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.
6. Insert the name for the tab in the input box.
7. Click OK.

Finally, you will see a tab created with the 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

• How to Group Tabs Under a Master Tab in Excel
• How to Change Worksheet Tab Color in Excel
• How to Put Excel Tabs on Top of Worksheet
• [Fixed!] Excel Sheet Tabs Hidden Behind Taskbar
• How to Unhide Sheet Tab in Excel

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