VBA Worksheets

Worksheets in Excel VBA

Worksheets in excel are the sheets which have rows and columns in which we work. Whenever we open excel we work on them. Now when working in excel we use functions they are called as worksheet functions and we can use similar functions in VBA. In this article, we will learn about various functions in VBA to work in the worksheet. The most important thing to remember is while working in VBA we have to always use the worksheet function as the data in excel is stored in the worksheet itself.

Worksheet is basically an object in VBA. Now there are two different methods of using this object. One is by referring to Worksheets method and another is by referring to Sheets method. Since this article is about worksheets we will move further with worksheets object. There are different objects to use a worksheet object. The proper way to use this object is explained below,

Worksheets( “ Sheet Name “ ). Methods

Now we need to remember all the different methods out there to use them in our code. We will capture some of the important and daily use of them by a few examples.

How to Use VBA Worksheets in Excel?

We will learn how to use a VBA Worksheets Function with few examples in excel.

Example #1 – VBA Worksheets

First, let us try to select a worksheet using the worksheet object in VBA.

Step 1: Before we move ahead we need to open VB Editor from the developer’s tab as shown in the image below, It is available in the code’s section

Step 2: Now once we click on it we are in the VB Editor and we can insert a new module from the insert tab where we will be writing all of our examples,

Step 3: Now define a subprocedure as shown below,

Code:

Sub Example1()

End Sub

Step 4: Since we can see that we have three different worksheets in our workbook as sheet 1, sheet 2 and sheet 3. Let us select sheet 2 for this example,

Step 5: The method to select sheet 2 using the worksheet object is as follows,

Code:

Sub Example1()

Worksheets("Sheet2").Select

End Sub

Select the method we are using for this object.

Step 6: Execute the above code and see the result in the workbook as follows,

We can see that sheet 2 has been selected.

Example #2 – VBA Worksheets

Now we have sheet 2 selected but we want to put data in cell A1 of sheet 1. To do this we can either select sheet 1 as shown above and then use the command to put a random value in the active sheet which is sheet 1. But that is twice the work. We can use the Activate method and put a random value.

Step 1: In the same module, define another subprocedure for our next example as follows,

Code:

Sub Example2()

End Sub

Step 2: First, let us activate sheet 1 using the worksheet object as follows,

Code:

Sub Example2()

Worksheets("sheet1").Activate

End Sub

Step 3: Now we will use the range property to put a random value in cell A1 as shown below,

Code:

Sub Example2()

Worksheets("sheet1").Activate
Range("A1").Value = 10

End Sub

Step 4: Execute the above code and see the result in sheet 1 as follows,

Since our selection was sheet 2 in the first example, This method not only selected sheet 1 for us but also we were able to put a value in cell A1.

Example #3 – VBA Worksheets

Now let us try to rename a worksheet name using the worksheet object. We will rename sheet 3 to Sheet Third.

Step 1: Again in the same module, define the next subprocedure for the third example,

Code:

Sub Example3()

End Sub

Step 2: In order to change the worksheet 3 name we need to use the .Name method as follows,

Code:

Sub Example3()

Worksheets("Sheet3").Name = "Sheet Third"

End Sub

Step 3: Now if we can see the original name of the sheet before this code as shown below,

Step 4: Execute the above code and see the result in the workbook as follows,

Example #4 – VBA Worksheets

Now in this method first we will add another worksheet manually using the + sign in the workbook and delete it using the worksheet object in VBA.

Step 1: First, let us create another worksheet in the workbook as shown below,

Step 2: We can see that now there is another sheet as sheet 4. Now define subprocedure for our fourth example.

Code:

Sub Example4()

End Sub

Step 3: To delete the worksheet from workbook we will use the .Delete method to worksheet object as shown below,

Code:

Sub Example4()

Worksheets("Sheet4").Delete

End Sub

Step 4: Now execute the above code again and we can see a pop up which asks us for the confirmation to delete the worksheet,

Step 5: Click on Delete and it will delete the sheet 4 for us.

Example #5 – VBA Worksheets

Now let us use another cool feature of this object. In this example, we will count the number of worksheets present in our workbook and display it.

Step 1: In the same module define another subprocedure as follows,

Code:

Sub Example5()

End Sub

Step 2: Declare a variable as an integer as follows,

Code:

Sub Example5()

Dim COunt As Integer

End Sub

Step 3: Now in Count store the value of the number of worksheets in the workbook using the count method as shown below,

Code:

Sub Example5()

Dim COunt As Integer
COunt = Worksheets.COunt

End Sub

Step 4: Display the value stored in the variable using the msgbox function as follows,

Code:

Sub Example5()

Dim COunt As Integer
COunt = Worksheets.COunt
MsgBox COunt

End Sub

Step 5: Execute the above code to get the count of worksheets in our workbook as follows,

Things to Remember

• Worksheets is an object in VBA.
• We use different methods to refer to different worksheets in VBA.
• If we enter the incorrect name of the sheets we will encounter an error.
• The argument in the worksheet object for sheet name is treated as a string.

Recommended Articles

This is a guide to VBA Worksheets. Here we discuss how to use Excel VBA Worksheets function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Copy Paste Function in VBA
2. Substring Excel Function
3. VBA Subscript out of Range
4. Excel ISNUMBER Formula