How to Create Tabs Automatically in Excel (3 Cases)

Opening new tabs one by one and naming them in Excel is tedious and time-consuming. But we can create tabs automatically using VBA codes.

In the GIF below, cells C6:C8 contain employee names, and a tab for each in this cell range is created automatically using a VBA macro in Excel.

Here are 3 different cases where we create tabs automatically in Excel:

Case 1 – Creating a Tab from Single Cell Value

To create a tab from a cell value automatically using VBA, you need to know the cell’s position, the value of which you use to create the tab. This method has one drawback: you cannot 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.

STEPS:

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.

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

Case 2 – Creating Tabs from Cell Range

We can also simultaneously create multiple tabs from the cell values of a certain cell range with VBA code. The code will create an input dialog box in which we specify the cell range, thus automating the process. You can create tabs from any cell range, including from a single cell value.

STEPS:

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.
5. Press the play button or F5 to run it.
6. Insert the cell range from which to create tabs in the input box.
7. Click OK.

Tabs are created automatically, named after the cell values of the selected cell range.

Case 3 – Creating Tabs with Custom Names

You can also create tabs automatically then name them in a dialog box after running the code, including with custom names that do not appear in your dataset.

STEPS:

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.

A tab will be created with the inserted name.

Read More: How to Create Tabs Within Tabs in Excel

Download Practice Workbook

Frequently Asked Questions

How do I create a daily tab in Excel?

To create a daily tab in Excel:

1. Insert the days of the week in a range 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 to be copied.
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. 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