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

What is Current Region Property in VBA

In this blog post, you will learn about CurrentRegion Property in Excel VBA.

CurrentRegion Property is useful when our dataset is continuously expanding. This property never asks to assign a whole range of datasets but a single cell reference from it.

What is the Current Region - Definition

The CurrentRegion is a range bounded by any combination of blank rows and black columns.

We will learn the current region property with the help of the below-given examples.

Suppose that we have a dataset, as shown in the image below:-

Now we will select or copy this image with the following VBA code.

Range (“A1:A10”).Select (for selecting the whole range from A1 to A10).

And for copying the above range:-

 Range(“A1:A10”).Copy (for copying the whole range from A1 to A10)

We can also select this dataset with the help of CurrentRegion property, see in the below image:-

You can see in the below image example that we took only single-cell reference for selecting the whole dataset.

These are the methods to copy and select the dataset for a single column dataset.

Now suppose that we expand our dataset up to (“A1:C10”), see in the below image:-

Now we will select the dataset again in the same way. 

For selecting and copying the above dataset, we will use the following VBA code:-

For Copying the dataset

Here you can see that we are using the range from "A1:C10".

But when we select or copy the above dataset with the CurrentRegion property, we do not mention the whole dataset range.

Current Region property is useful when we are working on expanding dataset.

By using the CurrentRegion property, we know that referring to a single cell can select or copy the whole range of the dataset.

But this property is not going to work if there is a blank row and column is existing within the dataset.

Now we will use the current region property to select or copy the below-given dataset.

In the below image, we use CurrentRregion property to copy the dataset, as you know that this property does not require to assign any range.

The whole dataset is divide into four parts because blank row and column.

We can see that only dataset which is beyond the blank row and the column is not selected or copied.

We can understand here the CurrentRegion property in the multiple examples as mentioned below.

The below dataset contains one blank row and a blank column, and with the help of this dataset, I want to explain that the definition of the CurrentRegion property, i.e how this property works/

Image -01

In the "Image-01" you can see that the CurrentRegion property not selecting or copying the dataset which is after the blank row and column.

 You can see in the below images, the CurrentRegion property is not doing anything with the dataset which is beyond the blank rows and blank columns.

Image - 02

Image - 03

Image - 04

So with the help of all these examples, I tried to explain the CurrentRegion property in a detailed way.

I hope you find this tutorial useful.

Please feel free to put your comments or suggestion into the below box

Thanks

Narendra