How To Create A New Sheet with Name in VBA

In our previous blog post, you learned about how to insert a new sheet before and after from any particular sheet by VBA.

Now in this post, you will learn about how to insert a new sheet with name from VBA macro. 

Here we are going to use the following code:-

Sub insert_new_sheet_with_name()

Sheets.Add.Name = "My Data"

End Sub

In the below image, you can see how this macro works.

When we execute this macro, our new sheet “My Data” added, see in the image below.

This one is the first method to insert a new sheet by VBA.

How To Insert A New Sheet With Name By Cell Range

In this method, you will learn about how to insert a new sheet by a cell value.

See in the below image, we have a cell value as “Excelnsg” in range (“A1”).

See in the below image-

Now we will create a new sheet in Excel by giving a name from the cell value.

Our sheet name is “Main” where our range “A1” has a value as “Excelnsg”.

Now we will use the following code to insert a new sheet with the name from a cell.

Sub insert_new_sheet_with_name_2()

Sheets.Add.Name = Range("A1").Value

End Sub

Write the above code in the VBA module and press 'F5' to execute the code and see the result in the below image.

After executing the code, see the result in the above picture. A new sheet with named as 'Range("A1") which is 'Excelnsg'.

So you learn, how to insert a new sheet in Excel with name by VBA macro.

I hope you find this tutorial useful.

Please feel free to post your comment and suggestion in the comment box below.

Thanks

Narendra.

Related Post

How to Insert a New Sheet In Excel VBA

What is Current Region Property in VBA

How to use Orientation in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

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

Easiest Ways to Copy and Paste in Excel VBA (3 Best Method)- In Hindi

How to use Orientation in Excel VBA

In this blog post, you will learn about “How to apply Orientation in Excel VBA”.

In this blog post, you will learn about “How to apply Orientation in Excel VBA”.

First of all, we need to know what is Orientation in Excel, so ‘Orientation’ is nothing but “aligning or rotating our text data in a cell”.

See the image below:-

See in the above image, our text data is rotating in 4 different direction.

In this post, you will learn more VBA option to rotate or align our text data.

I am using the below image as the text data:-

Our first option to rotate the text data from bottom to upward.

Option 01:- xlUpward

Code: -           Range ("A1:B1").Orientation = xlUpward

This code rotates our whole dataset from bottom to upward.

Option 02:- xlDownward

Code:- Range ("A1:B1").Orientation = xlDownward

This code will rotate our text data from upward to downward

Option 03:- xlVertical

Code:- Range ("A1:B1").Orientation = xlVertical

This code turns our whole dataset as vertical

    

Option 04:- xlHorizontal

This code will turn our whole dataset as horizontal

Code: - Range ("A1:B1").Orientation = xlHorizontal

Option 05:- Orientation = 30 (Degree)

This code will rotate our text data up to a 30-degree angle.

Code: - Range ("A1:B1").Orientation = 30

Option 06:- Orientation = 60 (Degree)

This code will rotate our text data up to a 60-degree angle.

Code: - Range ("A1:B1").Orientation = 60

Option 07:- Orientation = 90 (Degree)

This code will rotate our text data up to (90) degree angle.

Code: - Range ("A1:B1").Orientation = 90

Option 08:- Orientation = -90 (Degree)

This code will rotate our text data up to (-90) degree angle.

Code: - Range ("A1:B1").Orientation = -90

Option 09:- Orientation = 0 (Degree)

This code will rotate our text data up to (0) degree or Horizontal.

Code: - Range ("A1:B1").Orientation = 0

I hope you like this post.

Please feel free to put your comments below.

Regards

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Apply Border Style and Thickness in Excel VBA

How to Clear Cell Formatting in Excel VBA

Text Alignment in Excel VBA

How to use Wrap Text in Excel VBA

In this tutorial, you will learn about how to Use Wrap Text in Excel VBA.

What is Wrap Text?

If we have a single cell text length, reach up to multiple cells or columns and we want to adjust this text into a single cell.

Suppose that we have a text data in cell “A1” (See image below), and this text length reaches up to the next 3 cells or the next 3 columns. 

See image below:-

But we want to adjust this whole text data into a single cell.

Now with the help of Visual Basic, I will adjust the whole text in a single cell by using the “Wrap Text” method in VBA.

Go to the Developer tab and Click Visual Basic

Insert a new module and write the below-given code.

See the first image where our cell data exist in the original format.

In the above image, I have written two lines of code.

This is a simple code of converting our text data as “Wrap Text” and again convert this text as a normal mode by executing another line of code.

Now I will execute the code by pressing ‘F8’ function key for step by step execution.

By executing the first line as “True”, convert our text data as Wrap Text or adjust our all text data into one cell.

Again pressing “F8” for executing our second line of code, this convert our “Wrap Text” data into normal text data or in the usual format.

I hope this small tutorial help to understand this method of VBA to Wrap Text in Excel.

I hope you find this helpful.

Please feel free to put your comments to give me a small boost.

Regards

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Clear Cell Formatting in Excel VBA

Text Alignment in Excel VBA

Excel VBA - Orientation (In Hindi)