How to Insert a New Sheet In Excel VBA

In his blog post, you will learn to insert a new sheet or worksheet in Excel by VBA.

There are multiple ways to insert a new sheet in Excel.

First Method: 

Right-click on the sheet tab and choose the ‘insert’ option and then select the desired sheet type, whether a blank or different one.

See the image below:-

 

Second Method

Go to the “Home tab and select the ‘Cells’ group, click ‘Insert’ drop down and click on ‘Insert Sheet’. 

Third Method

Use shortcut key ‘Shft+F11’ to insert a new sheet.

Now, we will learn to insert or add a new sheet with the help of VBA macro.

In VBA, there are two methods to insert or add new sheets.

The first method to add a new sheet

The second method to add a new sheet

These are the two methods to insert or add a new sheet in Excel by VBA.

But by default, the sheet will be inserted or add just before the current sheet location.

Three sheets are appearing in the image below:-

Here, we will insert a new sheet before and after sheet “Main” to understand the concept in a better way.

In VBA, if we are going to insert a new sheet, then we need to give its location (such as previous or after) along with inserting a new sheet code.

Now here we want to insert a new sheet before sheet “Main” then our VBA macro will be as appears in the below image.

´

See the macro in the image and see that we have added Sheet7 before sheet Main.

Now we will insert a new sheet after sheet “Main” then our VBA code will be as seen in the below image:-

Here in the above image, you can see the sheet Sheet8 is inserted after sheet Main.

So in this tutorial, we learned how to insert a new sheet in VBA and how to insert a new sheet with its location. 

I hope you find this tutorial useful.

Please feel free to put your comments and suggestion regarding the above topic.

Thanks

Narendra