EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home VBA VBA Resources VBA Information Functions VBA Worksheets

VBA Worksheets

Madhuri Thakur
Article byMadhuri Thakur

Excel VBA WorkSheets

Worksheets in Excel VBA

Worksheets in excel are the sheets that have rows and columns in which we work. Whenever we open excel, we work on them. Now when working in excel, we use functions that are called worksheet functions, and we can use similar functions in VBA. In this article, we will learn about various functions in VBA to work on 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.

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

You can download this VBA Worksheet Excel Template here – VBA Worksheet Excel Template

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

VBA WorkSheet Example 1-1

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,

VBA Module Example 1.2

Step 3: Now define a subprocedure as shown below,

Code:

Sub Example1()

End Sub

VBA WorkSheet Example 1.3

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,

VBA WorkSheet Example 1.4

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

Code:

Sub Example1()

Worksheets("Sheet2").Select

End Sub

Example 1.5

Select the method we are using for this object.

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

VBA WorkSheet 1

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

VBA WorkSheet Example 2.1

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

Code:

Sub Example2()

Worksheets("sheet1").Activate

End Sub

VBA WorkSheet Example 2.2

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

Example 2.3

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

Example 2.4

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

VBA WorkSheet Example 3.1

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

VBA WorkSheet Example 3.2

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

VBA WorkSheet Example 3.3

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

Example 3.4

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,

VBA WorkSheet Example 4.1

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

Example 4.2

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

Code:

Sub Example4()

Worksheets("Sheet4").Delete

End Sub

Example 4.3

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,

Example 4.4

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

Example 4.5

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 them.

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

Code:

Sub Example5()

End Sub

VBA WorkSheet Example 5.1

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

Code:

Sub Example5()

Dim COunt As Integer

End Sub

VBA WorkSheet Example 5.2

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

Example 5.3

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

Example 5.4

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

VBA WorkSheet 4

Things to Remember

  • A worksheet 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 the Excel VBA Worksheets function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Vba Rename Sheet
  2. VBA Delete Sheet
  3. Vba Unprotect Sheet
  4. VBA Activate Sheet
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download VBA Worksheet Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW