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.


Download Practice Workbook


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.

How to Add Sheet After Current with VBA in Excel


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.

VBA to Add Default Named Sheet After Current Sheet

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.

VBA to Add Default Named Sheet After Current Sheet

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.

VBA to Add Default Named Sheet After Current Sheet

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

VBA to Add Default Named Sheet After Current Sheet

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


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

2. Add Sheet After Current One with Specific Name

  • 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: Excel VBA: Add Sheet After Last (3 Ideal Examples)


Similar Readings


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

Add Named Sheet Through Variable Assigning

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

Add Named Sheet Through Variable Assigning

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


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo