REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

VBA Activate Worksheet method is used to makes the current sheet as active sheet. Here we are the examples using Activate method of worksheet object in VBA. It is very frequently used method while writing VBA macros. We also see the VBA ActiveSheet object with examples.

VBA Activate Method Excel Worksheet Object

When we need to use Activate Worksheet method in VBA?

We use Activate worksheet method to activate current sheet as active sheet. When we are working with multiple sheets in a workbook, if we want to move or go to another sheet in the same workbook or another workbook we use activate worksheet method.

VBA Activate Worksheet Method- Syntax

Here is the example syntax to activate Worksheet using VBA. You can use either a Worksheet name or Worksheet number. Always best practice is to use sheet name.

Worksheets(“Your Worksheet Name”).Activate
‘Or
Worksheets(“Worksheet Number”).Activate

Where Activate is the method of Workbook object is used to makes current sheet as active sheet.

VBA Activate Worksheet – with Name: Example 1

Please see the below VBA codes to activate Worksheet. In this example we are activating a Worksheet named “Project1”.

Sub Activate_Sheet()
    Worksheets("Project1").Activate
    'Or
    Sheets("Project1").Activate
End Sub

VBA Activate Worksheet Method– with Number: Example 2

Please see the below VBA code or macro procedure to activate Worksheet. In this example we are activating first Worksheet in the active workbook.

Sub Activate_Sheet_BasedOnIndex()
    Worksheets(1).Activate
    'Or
    Sheets(1).Activate
End Sub

VBA ActiveSheet Object

We can refer the currently activated worksheet using Excel VBA ActiveSheet object. ActiveSheet VBA object is very usefull while automating tasks and working on currently active sheet in the active workbook window. If you ignore the ActiveSheet object while refering any other object like range or chart, VBA will treat the ActiveSheet as the current sheet by default. For example: Following are the two macro statements both will refer the active sheet.

ActiveSheet.Range("A1")="Some Value"
Range("A1")="Some Value"

both the above statements print some value at Range A1 of Activesheet.

Set ActiveSheet in Excel VBA

It is very convinient refer the Active Sheet in Excel VBA by setting into a Variable. We can assign and set ActiveSheet to an object and refer at any place of the procedure. Here is the syntax to Set ActiveSheet in VBA.

Sub sbSetAcitveSheetVBA()

Dim ws As Worksheet
Set ws = ActiveSheet

ws.Range("A1") = "Some Value"
End Sub

This code will print the some value at Range A1 of Activesheet.

VBA Activate Worksheet Method- Best Approach

Note: Always better to use the Worksheet name, instead of Worksheet number. The best is to assign the Worksheet to an object and then do whatever task you want to do with that particular Worksheet object.

When working with multiple Worksheets, you should refer the Worksheet with exact Worksheet name to correctly update your data into target Worksheet. Create Worksheet object and refer the Worksheet with the object whenever you require.

Let us see another example to understand the accessing the Worksheets using objects. You do not need to activate Worksheet to deal with any Worksheet.

Sub sb_Activate_Workbook_Object()
'Declare the objects here
    Dim wsMain As Worksheet, ws_A As Worksheet
    
'Set the worksheet to Object
    Set wsMain = ThisWorkbook
    Set ws_A = Worksheets("Test")

'Now deal with your worksheets
    ws_A.Range("A1") = wsMain.Sheet1.Range("A1")

End Sub

VBA Activate Worksheet Method- Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

  1. Open an Excel Worksheet
  2. Press Alt+F11 :This will Open the VBA Editor. Otherwise, you can open it from the Developer Tab
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled Worksheet
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and check how the sheet is activating.
Related Resource

You have seen how to activate worksheets using Excel VBA and referring the AciveSheet. Following are the example macros to Activate Excel Workbook and Range.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Last Updated: March 2, 2023

One Comment

  1. jojo February 14, 2019 at 7:15 PM

    Hello, in your last example you declare wsMain as WorkSheet and go on to set it as a Workbook and treat it as a Workbook, it this possible to do?

    Thanks for your very helpful work.

Leave A Comment