One of the things that VBA can be used to do is to create new worksheets. In this article, you learn how to create a new worksheet in Excel using VBA.

The ribbon

Lets start with how to create a Excel module and Subroutine to write the code. After opening the Excel file, click the developer tab.

And then Macros:

If no macro is present we can click Record Macro:

Then click Edit.

The macro

It will open the recorded macro as Macro1:

Create a new workbook. It is a single sheet in the file named as Sheet1.

Using the Sheets.Add Method

Use a very simple code like follows:

Sub Macro1()

   Sheets.Add After:=ActiveSheet
   Sheets("Sheet2").Select
   Sheets("Sheet2").Name = "new sheet"

End Sub

In this we have used 3 lines. First line will add a new sheet after the activesheet. Instead of Activesheet we can use the sheet name as sheets(“Sheet1”).

Naming the New Worksheet

Now we know that the new sheet created will be automatically named as “Sheet2”.

Sheets("Sheet2").Select
Sheets("Sheet2").Name = "new sheet"

So we selected the sheet2 and renamed it.

We can also use the sheet name : Sheets.Add After:=Sheets(“Sheet1”) if it already has more sheets then we could have used other sheets also.

Or we can use before also:

Sheets.Add Before:=Sheets("Sheet1")

If we run the code again it can give error because the new sheet created will not be “Sheet2”

So we change the reference as follows:

Newsheet_index = Sheets("Sheet1").Index + 1
Sheets(Newsheet_index).Select
Sheets(Newsheet_index).Name = "new sheet2"

You can also create a new worksheet and specify its location in the workbook using the following code:

Dim ws As Worksheet 

Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

 ws.Name = "My New Worksheet"

This code creates a new worksheet and adds it after the last worksheet in the workbook. The After argument of the Sheets.Add method is used to specify the location of the new worksheet.

This article has shown you how to create a new worksheet in Excel VBA. By following the steps in this article, you can automate the creation of new worksheets in Excel.

Related posts:

How to Create Vba Function in Excel What are the most common bugs in VBA code? How to run a SQL query in Excel VBA