How to Add Sheet After Current One with Excel VBA (3 Variants)

In terms of working with many sheets, we might face the necessity of adding a new sheet after the current one. We can use VBA for this purpose. In this article, I am going to explain 3 VBA codes to add sheet after current in Excel. I hope it will be helpful for the people facing the problem of adding a new sheet after the current one.

Basics of VBA Sheets.Add Method

Sheets.Add is a method that is used to add a new worksheet to the current worksheet. This method is used while writing VBA code.

Below are the parameters that you need to know about while working with this method.

3 Easy VBA Variants to Add Sheet After Current One in Excel

1. VBA to Add Default Named Sheet After Current Sheet

We can use simple VBA code with Sheets.Add method to add sheet after the current one. For the application, I have created a dataset named Personal Information. Then, I have labeled the worksheet as Personal Info.

Similarly, I have created another dataset named Working Information and labeled the worksheet Job Info.

Steps:

• Select a sheet after which you want to add another sheet. Here, I picked the Personal Info sheet.
• Go to the Developer tab.
• Then, click on Visual Basic from the ribbon.

A window named Microsoft Visual Basic for Applications will appear.

Alternatively, you can just press ALT + F11 to perform the same task.

• Followingly, click on Insert.
• From the available options, choose Module.

A Module will open.

• Next, write the following code in that module.

Sub New_Addition()
    Sheets.Add After:=Sheets("Personal Info")
End Sub

Here, I have created a Sub Procedure named New_Addition and applied the Sheets.Add method mentioning After particular sheet; Personal Info.

• Finally, click on the Run button or F5 to execute the VBA code.

Thus, we can add a new worksheet after the current one.

2. Add Sheet After Current One with Specific Name

We can also add a specifically named worksheet after the current worksheet. For this purpose, we need to specify the sheet name in the VBA code.

Steps:

• Pick a sheet as a current sheet after which you want to add another sheet. Here, I picked the Job Info.

• Create a new module following the method mentioned in the previous section.
• Now, input the following code.

Sub New_Addition()
    Sheets.Add(After:=Sheets("Job Info")).Name = "Holidays"
End Sub

I have created a Sub Procedure named New_Addition and applied the Sheets.Add method to add a new sheet after the Job Info sheet which was mentioned by After. I have mentioned the new sheet as Holidays.

• Lastly, click on the Run button or F5 to execute the VBA code.

We can see the newly added Holiday named sheet after the Job Info sheet.

Read More: How to Add Sheet with Name in Excel VBA

3. Add Named Sheet Through Variable Assigning

We can also create a string variable and declare it after the Sheets.Add method which will be the name of the newly added worksheet. Thus, we can pre-defined the name of the sheet name that will be added after the current sheet.

Steps:

• First of all, select a sheet after which you want to add another sheet. Here, I picked the Personal Info sheet.
• Next, go to the Developer tab.
• Then, click on Visual Basic from the ribbon.

• Create a Module following the method mentioned in the first method.
• After that, write the following VBA code on that module.

Sub NewAddition()
  Dim NameOfSheet As String
  NameOfSheet = "Improvements"
  Sheets.Add(, ActiveSheet).Name = NameOfSheet
End Sub

I have created a Sub Procedure named NewAddition. After that, I created a string variable named NameOfSheet and set its value as Improvements. Then, applied the Sheets.Add(, ActiveSheet) method to add a new sheet after the Personal Info sheet and call the variable as the sheet name.

• Finally, click on the Run button or F5 to execute the VBA code.

We can see the Improvements named new worksheet added after the Personal Info sheet.

Read More: Excel VBA to Add Sheet with Variable Name

Download Practice Workbook

Conclusion

That’s all for today. I have tried to explain 3 VBA codes to add sheet after current in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

Related Articles

• How to Add Sheet with Name from Cell Using Excel VBA
• How to Create New Sheet from Template Using Macro in Excel
• Excel VBA to Add Sheet If It Does Not Exist
• Excel Macro: Create New Sheet and Rename
• Excel Macro to Create New Sheet and Copy Data
• Excel VBA to Add Sheet to Another Workbook
• Excel VBA: Add Sheet After Last