How to Add Sheet with Name in Excel VBA (6 Easy Ways)

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.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


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.

Excel VBA Add Sheet with Name

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.

📌 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.

Excel VBA Add Sheet with Name

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.

Excel VBA Add Sheet with Name

  • 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 Sub

Code 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.

Excel VBA Add Sheet with Name

  • At this moment, press the Run (⏵) icon. Rather, you can press F5 on your keyboard.

Excel VBA Add Sheet with Name

  • 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.

Excel VBA Add Sheet with Name

  • So, our code will execute, and it will add a sheet named Profit.

Excel VBA Add Sheet with Name

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 to Another Workbook (3 Handy Examples)


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 Sub

Code Breakdown

  • Firstly, we are calling the Sub procedure as 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.

Excel VBA Add Sheet with Name

  • Afterward, run the code as shown in Method 1.
  • Thus, it’ll add a new sheet Balance Sheet before the sheet named Profit.

Excel VBA Add Sheet with Name

Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)


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:

  • At 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 Sub

This code is almost similar to the code of Method 2. Just, here we used the parameter After rather than Before. Because we want to add the new sheet succeeding a specific sheet.

Excel VBA Add Sheet with Name

  • Afterward, run the code as shown in Method 1.
  • Thus, it’ll add a new sheet Warehouse after the sheet named Profit.

Excel VBA Add Sheet with Name

Read More: Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)


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 Sub

Here, 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.

Excel VBA Add Sheet with Name

  • Then, run the code as shown in Method 1.
  • Hence, it’ll add a new sheet Company Profile at the start of the workbook.

Applying Visual Basic for Applications Code

Read More: Excel Macro: Create New Sheet and Rename (3 Ideal Examples)


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 Sub

Code 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.

Applying Visual Basic for Applications Code

  • 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.

Applying Visual Basic for Applications Code

Read More: Create New Workbook and Save Using VBA in Excel


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 of 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 Sub

Code Breakdown

  • Firstly, we are calling the Sub procedure as 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.

Applying Visual Basic for Applications Code

  • 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.

Applying Visual Basic for Applications Code

  • Finally, it will insert five sheets named after the cell values in the B5:B9 range. They all are after the sheet Sales Report.

Applying Visual Basic for Applications Code


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. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo