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

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 for this method.

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

Method 1 – VBA to Add Default Named Sheet After Current Sheet

For illustration, we have created a dataset named Personal Information and we have labeled the worksheet as Personal Info.

We 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. We have selected the Personal Info sheet.
Go to the Developer tab.
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.

Click on Insert.
From the available options, choose Module.

A Module will open.

Enter the following code in that module.

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

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

Click on the Run button or F5 to execute the VBA code.

A new worksheet will be added after the current one.

Method 2 – Add Sheet After Current One with Specific Name

Steps:

Select a sheet as a current sheet after which you want to add another sheet. We have selected the Job Info sheet.

Create a new module following the method mentioned in the previous section.
Enter the following code in the module.

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

We 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. We have mentioned the new sheet as Holidays.

Click on the Run button or F5 to execute the VBA code.

We can see the newly added sheet, Holidays after the Job Info sheet.

Method 3 – Add Named Sheet Through Variable Assigning

Steps:

Select a sheet after which you want to add another sheet. We have selected the Personal Info sheet.
Go to the Developer tab.
Click on Visual Basic from the ribbon.

Create a Module following the method mentioned in the first method.
Enter the following VBA code on that module.

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

We have created a Sub Procedure named NewAddition. we 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.