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:
- Go to the Developer tab > Visual Basic from Code group.
- Select Module from the Insert tab in the VBA window.
- 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
- Save the code.
- 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:
- Go to the Developer tab > Code group > Visual Basic.
- In the VBA window, select Module from the Insert tab.
- 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
- Save the code into the module.
- Press the play button or F5 to run it.
- Insert cell range to create tabs in the input box.
- 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:
- Go to the Developer tab > Visual Basic from Code group.
- Select Module from the Insert tab in the VBA window.
- 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
- Save the code.
- Press the play button or F5 to run the code.
- Insert the name for the tab in the input box.
- Click OK.
Finally, you will see a tab created with the inserted name.
Read More: How to Create Tabs Within Tabs in Excel
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:
- Insert the days’ input in a sheet and select the cell range.
- Insert a VBA code that creates tabs from the cell range automatically.
- 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:
- Go to the worksheet containing the format.
- Right-click on the sheet tab.
- Select the Move or Copy option.
- Tick the “Create a copy” checkbox in the Move and Copy dialog box.
- 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
Get FREE Advanced Excel Exercises with Solutions!
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?
Hello Adam! I checked the excel file and followed the steps mentioned in the article. It seems to work fine. Here, I am attaching a video for you, please follow this video and try again. If you face difficulties again, please send your excel file to [email protected].
https://www.exceldemy.com/wp-content/uploads/2023/08/Create-Tabs-Using-VBA.mp4
You can also read the article below to know more about VBA inputbox.
https://www.exceldemy.com/vba-inputbox-example/
Have a nice day!