Rafiul Haq
We can save valuable time by using the Excel VBA Macro. Often, we need to add new sheets and name them from a cell manually, but we can do it automatically by applying VBA. In this article, we will show you four methods to add a sheet with the name from a cell using Excel VBA.
Download Practice Workbook
4 Handy Approaches to Add Sheet with Name from Cell Using Excel VBA
This is the snapshot of the dataset from which we will extract the sheet name. We will get the values from the “ID” column for the first three methods to add a single sheet each time. Then for the last method, we will add six sheets by taking the values from the column “Name“.
1. Adding Single Sheet with Name from Cell
In this first method, we will add a sheet with the name from a cell using Excel VBA. By default, the added sheet will be on the left side of our reference. Moreover, we have named the original sheet “main“.
- First, we bring up the VBA Module window, where we type our codes.
- To do this, from the Developer tab → select Visual Basic. Alternatively, you can press ALT+F11 to bring up this too.
- So, the VBA window will pop up.
- Next, from the Insert tab, select Module.
- Here, we insert VBA code into Excel.
- After that, type the following code inside the VBA Module window.
Option Explicit
Sub Add_New_Sheet()
Worksheets("main").Activate
Sheets.Add.Name = Cells(5, 2).Value
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure as Add_New_Sheet.
- Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
- Next, we are using the Add.Name method to name the newly created sheet. Moreover, we have used the Cells property to refer to a cell value. Cells(5,2) signify row 5 and column 2, which means cell B5.
- Thus, this code works.
- So, our code will execute and it will add a sheet named “E-101”, which is taken from the cell value B5.
Read More: How to Create New Sheet from Template Using Macro in Excel
2. Applying Excel VBA to Add Sheet Name After Specific Sheet
We will add a sheet after a specific sheet using Excel VBA in this method.
2.1. After Specific Sheet
In this section, we will add a sheet after the sheet named “E-101“.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Add_New_Sheet_After_Specific_Sheet()
Worksheets("main").Activate
Sheets.Add(After:=Sheets("E-101")).Name = Range("B7").Value
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure as Add_New_Sheet_After_Specific_Sheet.
- Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
- Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created after the “E-101” sheet that we created in the last method. Moreover, we have used the Range property to refer to the cell value from cell B7, which is “E-103”.
- Thus, this code works.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will add a sheet named “E-103” after the “E-101” sheet.
2.2. After Last Sheet
In this section, we will add a sheet after the last sheet of the workbook.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Sheet_Start_Workbook()
Worksheets("main").Activate
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Range("B10").Value
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure as Add_New_Sheet_After_Specific_Sheet.
- Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
- Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created after the last sheet from the workbook. This last sheet number will be obtained from the “Sheets.Count” property. Moreover, we have used the Range property to refer to the cell value from cell B10, which is “E-106”.
- Thus, this code works by adding a sheet with the name value from a cell.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will add a sheet after the last sheet.
Read More: Excel VBA: Add Sheet After Last (3 Ideal Examples)
3. Adding Sheet Before Specific Sheet with Name from Cell
In this method, we will add a sheet with a name from a cell before a specific sheet in Excel using a VBA Macro.
3.1. Before Specific Sheet
We will insert a sheet with the name from a cell before a specific sheet by applying Excel VBA.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Add_New_Sheet_Before_Specific_Sheet()
Worksheets("main").Activate
Sheets.Add(Before:=Sheets("E-103")).Name = Range("B6").Value
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure as Add_New_Sheet_Before_Specific_Sheet.
- Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
- Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created before the sheet called “E-103” from the workbook. Moreover, we have used the Range property to refer to the cell value from cell B6, which is “E-102”.
- Thus, this code works by adding a sheet with the name value from a cell.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will add a sheet “E-102” before the “E-103” sheet.
3.2. Start of Workbook
We will insert a sheet before the first sheet in Excel using VBA in this section.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Sheet_Start_Workbook()
Worksheets("main").Activate
Sheets.Add(Before:=Sheets(1)).Name = Cells(8, 2).Value
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure as Sheet_Start_Workbook.
- Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
- Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created before the first sheet from the workbook. This action will be executed before the first sheet, as denoted by the “Sheets(1)” property. Moreover, we have used the Cells property to refer to a cell value. Cells(8,2) signify row 8 and column 2, which means cell B8.
- Thus, this code works by adding a sheet with the name value from a cell.
- Then, as shown in method 1, we Save and Run this Module.
- Thus, this code will add a sheet before the first sheet.
Read More: Excel Macro: Create New Sheet and Rename (3 Ideal Examples)
4. Inserting Multiple Sheets with Name from Cell Using Excel VBA
For the last method, we will add multiple sheets in the workbook with the name from a range of cells using Excel VBA. Moreover, we will ask the user for input on the range to take the name from. Lastly, we will also be using a For Each Next loop to go through all the cells.
Steps:
- Firstly, as shown in the first method, bring up the Module window.
- Secondly, type the following code inside that.
Option Explicit
Sub Add_Sheets_from_Cell_Value()
Dim xRange As Range
Dim qq As Range
Set xRange = Application.InputBox("Select Cell Range" _
& "to Create Sheets", "ExcelDemy", Type:=8)
Application.ScreenUpdating = False
For Each qq In xRange
On Error Resume Next ' This will ignore any error
Sheets.Add(After:=Sheets(Sheets.Count)).Name = qq.Value
Next qq
Application.ScreenUpdating = True
End Sub
VBA Code Breakdown
- Firstly, we are calling the Sub procedure as Add_Sheets_from_Cell_Value.
- Then, we define the variable types.
- Afterward, we use an InputBox to get the range data from the user.
- Then, we use a For Each Next loop to go through the selected cell range one-by-one.
- Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created after the last sheet from the workbook. This last sheet will be found out by the “Sheets.Count” property. Moreover, we have added the “On Error Resume Next” statement before the loop. This will ignore any errors and continue the looping process.
- Thus, this code works by adding a sheet with the name value from a cell.
- Then, as shown in method 1, we Save and Run this Module.
- Afterward, this code will ask the user to input the range.
- Then, select the cell range C5:C10 and press OK.
- As a result, it will create six new sheets in the workbook. Additionally, we have set the code to ignore any errors. So, if your cell range has duplicate values, then this code will not stop; rather, for that duplicate value, it will give a generic name (for example “sheet16”) to the newly created sheet.
Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)
Things to Remember
- Firstly, the worksheet name cannot be blank.
- Secondly, sheet names can contain a maximum of 31 characters.
- Thirdly, there should not be any slashes, question marks, star marks (multiple signs), third brackets, or colons.
- Next, the sheet name should begin or end with an apostrophe.
- Lastly, you should not name the sheet with reserved words such as “History” in Excel.
Conclusion
We have shown you four quick ways to add a sheet with a name from a cell in Excel using VBA. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
Related Articles
- Excel VBA to Add Sheet to Another Workbook (3 Handy Examples)
- Create New Workbook and Save Using VBA in Excel
- Excel VBA: Create a New Workbook and Name It (6 Examples)
- Macro to Create New Sheet and Copy Data in Excel (4 Examples)
- Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)
- How to Add Sheet After Current One with Excel VBA (3 Variants)
