spot_img

How To Create A New Worksheet In Excel VBA

In this article we will learn how to create a new worksheet in Excel using VBA.

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

developer ribbon button

And then macros:

macros ribbon button

If no macro is present we can click record macro and stop macro and then click macros:

record macro ribbon button

Then click Edit.

macro1 run

It will open the recorded macro as macro1 already created:

basic macro

Now under this module we can learn how to create a new workbook. At present we have only single sheet in the file named as “Sheet1”

sheet1

We can 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”).

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.

new sheet

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"

Template

You can download the Template here – Download
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell