Md. Araf Bin Jayed
While working in Microsoft Excel, users sometimes 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. So, in this article, we will show you how to create tabs automatically in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
3 Handy Ways to Create Tabs Automatically in Excel
In this article, you will see three separate methods to create tabs automatically in Excel. We will use three different VBA codes in those three methods, respectively. By using VBA, we don’t have to insert tabs manually or use any shortcuts repeatedly. By giving some proper commands, we can easily complete our tasks through VBA coding. In our first VBA code, we will insert a certain cell range from our data set for creating tabs. Then in the second one, we will specify a cell value, after which the new tabs will open. Lastly, we will use a certain VBA code in which we will insert a tab name as per our desire. For working purposes, we will use the following data set.
1. Inserting Cell Range with VBA to Create Tabs Automatically
In our first method with VBA, we will insert a certain cell range to create tabs. For that, we will create an input dialogue box through VBA coding and then specify the cell range in the input box. The cell range will depend on the working purposes of the users. You can find the details of this procedure in the following.
Step 1:
- First of all, after selecting the data set, go to the Developer tab of the ribbon.
- Then, from the Code group, choose the command named Visual Basic.
Step 2:
- Secondly, you will see the VBA window.
- Then, from the Insert tab select Module.
Step 3:
- Thirdly, copy the following VBA code into the module.
'Set the function name
Sub Inserting_Cell_Range_with_VBA()
'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:="Choosing Cell Range:", _
Title:="Inserting_Cell_Range_with_VBA", _
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
VBA Breakdown
- The function name is Inserting_Cell_Range_with_VBA.
- The variable names are rng and Cell and they are all Range type variables.
- On Error GoTo Errorhandling: If the user chooses anything other than a cell range then the procedure will go to Errorhandling.
- Set rng = Application.InputBox(Prompt:=”Choosing Cell Range:”, _Title:=”Inserting_Cell_Range_with_VBA”, _Default:=Selection.Address, Type:=8): Users will call an input box through this line. Then they will choose the cell range in the input box.
- For Each Cell In rng: This will reserve every cell value into the cell object.
- If Cell <> “” Then: This command will see if the cell is empty or not. If the cell is empty, then the procedure will end.
- Sheets.Add.Name = Cell: A new sheet will be created which will be named after the cell value.
- End If: Stops the IF function.
- Next Cell: Go on with the next cell in the cell range.
- Errorhandling:: If an error occurs, then the code will go to this line.
Step 4:
- Fourthly, save the code into the module and press the play button or F5 to run it.
Step 5:
- Fifthly, you will see the Inserting_Cell_Range_with_VBA dialogue box.
- Then, you have to input the specific cell range from the data set as per your desire.
- For our example, the cell range will be C5:C8.
- Lastly, click OK.
Step 6:
- Finally, you will be able to create tabs automatically after the name of those cells in the cell range.
Read More: How to Insert Sheet from Another File in Excel (3 Easy Ways)
2. Specifying Cell Values with VBA to Create Tabs Automatically in Excel
In our second approach, we will specify a single cell value in the VBA code. The newly created tab will be named after this specific cell value. We will not use any input dialogue box in this method. Rather, we will address the cell position directly in the VBA code. The detailed steps for this procedure are as follows.
Step 1:
- In the beginning, take the following data set to apply the code.
Step 2:
- Secondly, open the VBA window like in the previous method.
- Then, in the module, paste the following VBA code.
- In our code, you can see the specific cell address is (7,2).
- Consequently, it indicates the cell that is at the intersection of the seventh row and the second column.
- Furthermore, the cell value of this cell is 34765-47.
'Set the function name
Sub Specifying_Cell_Value_with_VBA()
'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(7, 2).Value
End Sub
VBA Breakdown
- The function name is Specifying_Cell_Value_with_VBA.
- Worksheets(“Cell Value”).Activate: This line will activate the sheet that we are currently working on.
- Sheets.Add.Name = Cells(7, 2).Value: This command will create a new sheet and the name will be based on the specific cell value.
Step 3:
- Thirdly, you have to save the code for further use.
- Then, press the play button or F5 to run the code.
Step 4:
- Finally, you will create a tab named after the specific cell value from the code.
Read More: How to Create Multiple Worksheets from a List of Cell Values
3. Inserting Tab Names with VBA to Create Tabs Automatically
In our previous two methods, it was not necessary to give the tab name manually. But if you want to name the tab as per your desire after opening it with a VBA code, then you can follow this last method. Here, you will create the tab automatically and name them in the dialogue box after running the code. For a better understanding, go through the following steps.
Step 1:
- Firstly, select your data set for the procedure.
Step 2:
- Secondly, in the module of the VBA window, paste the following code.
'Set the function name
Sub Inserting_Tab_Name_with_VBA()
'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", _
"Inserting_Tab_Name_with_VBA")
'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
VBA Breakdown
- The function name is Inserting_Tab_Name_with_VBA.
- The first variable name is tab_name and it is a string type variable and the second variable name is sheet and it is an object type variable.
- On Error Resume Next: If any error occurs then, this statement will overlook it.
- tab_name = InputBox(“Enter the Tab Name”, _”Inserting_Tab_Name_with_VBA”): Users will call an input box through this line. This input box will take the name form user for the newly created tabs.
- If tab_name = “” Then Exit Sub: If the user keeps the input box empty, then no tab will be created.
- Sheets.Add.Name = tab_name: Finally, the user will be able to create a new tab with his preferred tab name.
Step 3:
- Thirdly, save and run the following code.
Step 4:
- Fourthly, you will see the Inserting_Tab_Name_with_VBA dialogue box.
- Then, in the Enter Tab Name dialogue box, insert any name as per your choice.
- For our example, we will input the name, Caroline.
- Lastly, press OK.
Step 5:
- Finally, you will see a tab named Caroline after pressing OK.
Read More: How to Create Multiple Sheets in Excel with Different Names
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to create tabs in Excel automatically by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.
