How to Create New Excel Sheet in VBA

1. Create New Excel Sheet in VBA
2. Use the Sheets.Add() Method to Add Sheet in VBA

We will introduce how to create a new excel sheet in VBA with examples.

Create New Excel Sheet in VBA

Writing a VBA code to insert a new worksheet in a workbook is one of the smartest things we can do once we start learning VBA. There is a shortcut key to insert a new worksheet, or we can use the standard option, but the advantage of using a VBA code is that we can add multiple worksheets with a click and specify where we wish to add them.

We’ll need to use Sheets for this. In this article, we’ll learn how to apply the Add technique to add one or more worksheets to a workbook.

Use the Sheets.Add() Method to Add Sheet in VBA

We can add a fresh sheet before an already existing sheet by using the before method. Add another sheet after an existing one using the after method.

The added sheet must be counted by using the count method. We want to add the sheet type using the type method.

We can use the syntax for Sheets.Add().

# vba
Sheets.Add ([Before], [After], [Count], [Type])

Follow these instructions in the VBA editor to insert a new sheet, as shown below.

1. We must first enter Sheets in VBA.
2. We will use Add method.
3. Then, we must decide where to position the new sheet (Before or After) by using VBA.
4. The next step is to input the total number of worksheets in excel by using VBA.
5. Lastly, we will enter the sheet’s type.

Let’s create a function to add a new sheet using the steps we discussed above.

Code:

# vba
Sub CreateNewSheet()
Sheets.Add After:=Worksheets("Sheet2"), Count:=3, Type:=xlWorksheet
End Sub

Output:

We will use different methods to insert the fresh sheet into a Workbook. Several methods for adding an extra sheet to a workbook are listed below.

Add Single Sheet in VBA

We can use the code below to add a single sheet without specifying any arguments.

Code:

# vba
Sub AddSingleSheet()
ActiveWorkbook.Sheets.Add
End Sub

Output:

This code instructs Excel to add a sheet to the active workbook. However, because no arguments are provided, it will take the default values to add one worksheet (Microsoft excel sheet) before the active sheet.

Here is another way to write this.

Code:

# vba
Sub AddSingleSheetWithoutActiveWorkbook()
Sheets.Add
End Sub

Output:

Insert Several Sheets in VBA

Provide the number of sheets we want to add in the COUNT option to add many sheets at once.

Code:

# vba
Sub AddMultipleSheets()
Sheets.Add Count:=2
End Sub

Output:

Insert Sheet With a Title in VBA

We can enter the following code to rename the sheet once we have added it.

Code:

# vba
Sub AddSheetWithTitle()
Sheets.Add.Name = "New Sheet"
End Sub

Output:

Create New Sheet With the Name of a Cell in VBA

We may also utilize a cell to get the value for the sheet’s name.

Code:

# vba
Sub AddSheetWithCellName()
Sheets.Add.Name = Range("B1")
End Sub

Output:

In excel, Cell A1 is applied in the above code to get the new sheet’s name.

Add New Sheet at Start in VBA

We can also insert a sheet to the start of the sheets in the workbook by applying the above argument.

Code:

# vba
Sub BeginByNewSheet()
Sheets.Add Before:=Sheets(1)
End Sub

Output:

Instead of specifying the sheet name, we will supply the sheet number. We used sheet number (1) in the above code to tell VBA to add the sheet just before the sheet in the first place in all worksheets.

This ensures that the fresh sheet is always added first.

Add New Sheet at End in VBA

We must rewrite the code above to add a new sheet at the end.

Code:

# vba
Sub AddSheetAtEnd()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub

Output:

We’ll need to know how many sheets are in the workbook to add a new sheet at the end.