EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home VBA VBA Resources VBA Information Functions VBA Activate Sheet

VBA Activate Sheet

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Activate Sheet

Excel VBA Activate Sheet

For activating or selecting a sheet in excel, we manually go to the sheet and click on the Sheet tab. But when we write a code where we need to link the different sheets in different forms, then we can use the VBA Activate Sheet function. This can be done with the help of the “.Activate” command in VBA. This will automatically select or activate the sheet for further use.

Activating a sheet means selecting that sheet. And to know whether that sheet is activated or selected, it is better to check after running the code in excel if we are able to see the current sheet or not.

Watch our Demo Courses and Videos

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

How to Activate the Sheet in Excel VBA?

Below are the different examples to activate sheet in excel using VBA code.

You can download this VBA Activate Sheet Excel Template here – VBA Activate Sheet Excel Template

VBA Activate Sheet – Example #1

There are various ways to activate a sheet in VBA. For this, we have considered 3 sheets named as First, Second and Third as shown below. Purpose of naming each sheet is to distinguish between sheet number and sheet name, which will be used in upcoming examples.

VBA Activate Sheet Example 1-1

Follow the below steps to activate sheet in Excel VBA.

Step 1: Now go to VBA and from the Insert menu tab, select Module as shown below.

VBA Activate Sheet Example 1-2

The module is the place where we will write the code for Activating Sheet. After that, we will get a new module opened.

Step 2: In that module, create Subcategory of function in any name. For better understanding, we have created it in the name of VBA Activate Sheet in sequence. This will help in tracking the proper module.

Code:

Sub VBA_ActivateSheet1()

End Sub

VBA Activate Sheet Example 1-3

Step 3: Now use Sheets function for selecting any sheet.

Code:

Sub VBA_ActivateSheet1()

Sheets("First")

End Sub

VBA Activate Sheet Example 1-4

Step 4: Now to actually selecting or activating the sheet in excel use Activate command trailed by dot (.)

Code:

Sub VBA_ActivateSheet1()

Sheets("First").Activate

End Sub

VBA Activate Sheet Example 1-5

By this, we allow the sheet named as “First” to get selected whenever the code calls for it.

Step 5: Now do the same for sheet name Second and Third as shown below.

Code:

Sub VBA_ActivateSheet1()

Sheets("First").Activate
Sheets("Second").Activate
Sheets("Third").Activate

End Sub

VBA Activate Sheet Example 1-6

Step 6: Now compile the code step-by-step or in one go. The small code can be compiled in one go which may not give an error. After that run the code by clicking on the Play button which is below the menu bar.

VBA Activate Sheet Example 1-7

We will see that the Third Sheet will get selected as it was located at the end. If we do step-by-step compile then we will be able to see who First, Second and Third Sheet are getting activated by pressing F8 Key.

As the compiler passes through the first line, we will see sheet First is activated as shown below.

VBA Activate Sheet Example 1-8

Again pressing F8 Key, the compiler will pass through the second line of code and we can see sheet Second is activated.

Second Sheet Example 1-9

And when compiler reached to End Sub of the code structure, we will sheet Third is activated as shown below.

Third Sheet Example 1-10

VBA Activate Sheet – Example #2

We can also use Worksheet command to select and activate any sheet. This process is quite similar to example-1. For this again we will consider the file sheet name First, Second and Third. Follow the below steps to activate sheet in Excel VBA.

Step 1: In Module, start writing Subcategory of VBA Activate Sheet in sequence as shown below.

Code:

Sub VBA_ActivateSheet2()

End Sub

VBA Activate Sheet Example 2-1

Step 2: As we used Sheet command for activating Sheet, here we will use Worksheet command.

Code:

Sub VBA_ActivateSheet2()

Worksheets("First")

End Sub

VBA Activate Sheet Example 2-2

Step 3: Now use Activate function trailed by dot(.) here as well to activate the sheet.

Code:

Sub VBA_ActivateSheet2()

Worksheets("First").Activate

End Sub

VBA Activate Sheet Example 2-3

Step 4: Now do the same for Sheet Second and Third as well.

Code:

Sub VBA_ActivateSheet2()

Worksheets("First").Activate
Worksheets("Second").Activate
Worksheets("Third").Activate

End Sub

VBA Activate Sheet Example 2-4

Step 5: Once done then compile the code. Here we will do step-by-step compile to see the changes. For press F8 which does step-by-step compiling and run the code as well. As the compiler passes through the second line of code, we will see in the Excel sheet Second is activated or selected.

Second Sheet Example 2-5

Step 6: Again press the F8 key to select the next sheet. We will see, as compiler passes through the third line of code and reaches to end, sheet named Third is activated or selected.

Third Sheet Example 2-6

VBA Activate Sheet – Example #3

There is another way to activate any worksheet. For this also we will consider the same set of sheets with same names First, Second and Third as used in the above examples. In this example, we will see 3 combinations of Sheet and Worksheet function along with Select, which we will use in place of Activate. The select function works in the same manner as Activate. Using a combination of different function for the same use we will see how the output varies.

Follow the below steps to activate sheet in Excel VBA.

Step 1: For this open a module and put the subcategory in the name of the performed function or in any other name as per your choice. We are keeping the name but in sequence, as shown below.

Code:

Sub VBA_ActivateSheet3()

End Sub

VBA Activate Sheet Example 3-1

Step 2: Again in the same manner use Sheets function with sheet name as shown below.

Code:

Sub VBA_ActivateSheet3()

Sheets("First")

End Sub

VBA Activate Sheet Example 3-2

Step 3: After that use Select function trailed by dot (.)

Code:

Sub VBA_ActivateSheet3()

Sheets("First").Select

End Sub

VBA Activate Sheet Example 3-3

Step 4: Now in the second line of code, we will use Sheets function along with Activate for sheet named Second as shown below.

Code:

Sub VBA_ActivateSheet3()

Sheets("First").Select
Sheets("Second").Activate

End Sub

VBA Activate Sheet Example 3-4

Step 5: And for the last sheet named as Third we will use Worksheets along with Select function to activate it as shown below.

Code:

Sub VBA_ActivateSheet3()

Sheets("First").Select
Sheets("Second").Activate
Worksheets("Third").Select

End Sub

VBA Activate Sheet Example 3-5

Step 6: Now compile the complete code and run the code step-by-step to see the changes by pressing the F8 key. As the compiler passes through the first line of code, sheet First will be activated.

First Sheet Example 3-6

As the compile goes through the second line of code, sheet Second will be activated.

Second Sheet Example 3-7

And at last, when compiler comes to End Sub, sheet Third will get activated as shown below.

Third Sheet Example 3-8

This is how we can use a combination of Sheets and Worksheets along with Activate and Select functions to activate any worksheet.

Pros of Excel VBA Activate Sheet

  • Selecting any sheet in excel as well as with VBA is quite easy.
  • This function is quite helpful when our code is linked with data with different worksheets.

Things to Remember

  • Select and Activate function has the same work of activating any worksheet. Although the name is different.
  • Different combination of Select and Activate along with Sheets and Worksheet is possible for activating one or multiple worksheets.
  • Activating any sheets means selecting of that worksheet.
  • Once done with implementing code, save the file in Macro Enable Worksheet. This helps in saving the code so that we can use it multiple times without any loss.

Recommended Articles

This is a guide to VBA Activate Sheet. Here we discussed how to Activate sheet in Excel using VBA code along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Range Cells
  2. VBA RGB
  3. VBA Name Worksheet
  4. VBA Delete Sheet
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
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.

Let’s Get Started

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

EDUCBA

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

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