This article provides the answers to your questions on how to add a sheet with name in Excel VBA. If you are looking for such unique kinds of tricks, you’ve come to the right place. Here, we’ll discuss 6 easy & proven ways of adding a sheet with a name in Excel VBA.
VBA Code to Add Sheet with Name
We’ll use the Sheets object to add a sheet in Excel. The fundamental VBA code to add a sheet with the name looks like the one below.
Sheets.Add ([Before], [After], [Count], [Type])
Before: It’s optional. It adds a new sheet prior to a specific sheet.
After: It’s optional too. It adds a new sheet after a specific sheet.
Count: It’s also an optional parameter. It indicates the number of sheets to add.
Type: It’s optional also. It specifies the type of sheet. Here, the default value is xlWorksheet.
6 Different Ways to Add Sheet with Name in Excel VBA
In Excel, we can add a new sheet with the add ⊕ icon just beside the sheet name. Have you ever thought of automating the same task in Excel? Think no more, because VBA has you covered.
Here we have a worksheet named Sales Report. It contains the Daily Sales Report of a certain cafeteria. In columns B, C and D, there are names of Sales Reps, Items, and Quantity respectively.
Here, we’ll add other sheets in this workbook with the help of VBA macro.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
1. Adding Sheet with Name in Excel VBA
In our first method, we’ll add a sheet with the name from the user input. If you are curious to learn, follow these simple steps.
- At the very beginning, go to the Developer tab.
- Secondly, select Visual Basic on the Code group.
- Alternatively, press ALT + F11 to do the same task.
Note: Make sure to press the ALT key at the left of the SPACEBAR. The operation wouldn’t execute if you use other ALT keys.
- Instantly, the Microsoft Visual Basic for Applications window opens.
- Later, move to the Insert tab.
- Then, select Module from the options.
- Presently, it opens the code Module.
- Afterward, write the following code in the Module.
Sub Add_Sheet_with_Name()
Dim sheet_name As String
Dim sheet As Object
On Error Resume Next
sheet_name = InputBox("Please enter sheet name ", _
"Exceldemy")
If sheet_name = "" Then Exit Sub
Sheets.Add.Name = sheet_name
End SubCode Breakdown
- Firstly, we are calling the Sub procedure as Add_Sheet_with_Name.
- Then, we define the variable types.
- Later, we added the On Error Resume Next statement. This will ignore any errors.
- Afterward, we used InputBox to get the name of the sheet from the user. Also, we stored the text string from the InputBox into the sheet_name variable.
- Next, if the sheet_name is empty, then the code won’t proceed.
- Finally, we are using the Add.Name method to name the newly created sheet. Here, the name will be the same as the sheet_name variable.
- Suddenly, it opens an input box.
- Here, enter the name of the new sheet. In this case, we wrote Profit in the box.
- Later, click OK.
- So, our code will execute, and it will add a sheet named Profit.
Note: We haven’t mentioned any place for our sheet. By default, it’s placed before the active sheet.
Read More: Excel VBA to Add Sheet with Variable Name
2. Applying Excel VBA to Add Sheet with Name Before Specific Sheet
In our second method, we’ll add a sheet with the name before a specific sheet. Let’s explore the process step by step.
📌 Steps:
- Firstly, bring up the code Module window as shown in Method 1.
- Secondly, write the following code into that.
Sub Add_Sheet_Before_Specific_Sheet()
Worksheets("Sales Report").Activate
Sheets.Add(Before:=Sheets("Profit")).Name = "Balance Sheet"
End SubCode Breakdown
- Firstly, we are calling the Sub procedure Add_Sheet_Before_Specific_Sheet.
- Then, we activate the Sales Report 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 Profit from the workbook. Moreover, we have named the sheet as Balance Sheet.
- Afterward, run the code as shown in Method 1.
- Thus, it’ll add a new sheet Balance Sheet before the sheet named Profit.
Read More: Excel VBA to Add Sheet If It Does Not Exist
3. Using Excel VBA to Add Sheet with Name After Specific Sheet
In this method, we’ll add a sheet after the sheet named Profit. Let’s see the process in detail.
📌 Steps:
- First, bring up the code Module window as shown in Method 1.
- After that, write the following code into that.
Sub Add_Sheet_After_Specific_Sheet()
Worksheets("Profit").Activate
Sheets.Add(After:=ActiveSheet).Name = "Warehouse"
End SubThis code is almost similar to the code of Method 2. Here, we used the parameter After rather than Before. Because we want to add the new sheet succeeding a specific sheet.
- Afterward, run the code as shown in Method 1.
- Thus, it’ll add a new sheet Warehouse after the sheet named Profit.
Read More: How to Add Sheet After Current One with Excel VBA
4. Inserting Sheet with Name at Start of Workbook
In this section, we will insert a new sheet at the start of the workbook in Excel using VBA. So, without further delay, let’s see how we do it.
📌 Steps:
- Initially, bring up the code Module window as shown in Method 1.
- Afterward, write the following code into that.
Sub Add_Sheet_Start_Workbook()
Sheets.Add(Before:=Sheets(1)).Name = "Company Profile"
End SubHere, we’re adding the new worksheet before the first sheet of the workbook. That means at the start of the workbook. As a result, it’s the first sheet now. Also, we named the sheet Company Profile.
- Then, run the code as shown in Method 1.
- Hence, it’ll add a new sheet Company Profile at the start of the workbook.
Read More: Excel Macro: Create New Sheet and Rename
5. Utilizing Excel VBA to Add Sheet at the End of Workbook
In this section, we will add a sheet after the last sheet of the workbook. Allow me to demonstrate the process below.
📌 Steps:
- Primarily, open the code Module window as shown in Method 1.
- Secondly, paste the following code into that.
Sub Sheet_End_Workbook()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Income Statement"
End SubCode Breakdown
- Firstly, we are calling the Sub procedure as Sheet_End_Workbook.
- Next, we are using the Add.Name method to name the newly created sheet. We will create this sheet after the last sheet of the workbook. We can obtain the last sheet number from the Sheets.Count property. Moreover, we have named the sheet Income Statement.
- After that, run the code as shown in Method 1.
- Hence, it’ll add a new sheet Income Statement at the end of the workbook.
Read More: Excel VBA to Add Sheet to Another Workbook
6. Introducing Multiple Sheets Using Cell Value by 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. So, let’s have a look at the steps below.
📌 Steps:
- At first, open the code Module window as shown in Method 1.
- Afterward, copy the following code and paste it into that.
Sub Add_Multiple_Sheets_Using_Cell_Value()
Dim rng As Range
Dim cc As Range
Set rng = Application.InputBox("Select Cell Range" _
& " to Insert Sheets", "ExcelDemy", Type:=8)
Application.ScreenUpdating = False
Worksheets("Sales Report").Activate
For Each cc In rng
Sheets.Add(After:=ActiveSheet).Name = cc.Value
Next cc
Application.ScreenUpdating = True
End SubCode Breakdown
- Firstly, we are calling the Sub procedure Add_Multiple_Sheets_Using_Cell_Value.
- Then, we define the variable types.
- Afterward, we use InputBox to get the range of data from the user.
- Fourthly, we activate the sheet Sales Report.
- Later, 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. We will create these sheets after the active sheet Sales Report of the workbook.
- Now, run the code as shown in Method 1.
- Instantly, the Exceldemy input box opens.
- Then, give the cells in the B5:B9 range in the box.
- Lastly, click OK.
- Finally, it will insert five sheets named after the cell values in the B5:B9 range. They all are after the sheet Sales Report.
Read More: How to Add Sheet with Name from Cell Using Excel VBA
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
This article provides easy and brief solutions to adding sheets with names by Excel VBA. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.
Related Articles
- Excel Macro to Create New Sheet and Copy Data
- How to Create New Sheet from Template Using Macro in Excel
