Activate Worksheet is used to makes the current sheet as active sheet. Here we are using Activate method of worksheet object. It is very frequently used method while writing VBA macros.

PREMIUM TEMPLATES
LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack

Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template

Excel Template

VIEW DETAILS

Ultimate Project Management Template

Excel Template

VIEW DETAILS

20+ Excel Project Management Pack

Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack

PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack

Word Templates

VIEW DETAILS

VBA Activate Method Excel Worksheet Object

In this topic:

  • Why we need to Activate a Worksheet?
  • VBA Activate Worksheet – Syntax
  • VBA Activate Worksheet Method: Example 1
  • VBA Activate Worksheet Method: Example 2
  • VBA Activate Worksheet Method- Best Approach
  • VBA Activate Worksheet Method- Instructions

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

LIMITED TIME OFFER – Get it Now!
Advanced Project Plan Excel Template

 
Related Resource

Excel VBA ReferenceProject Management Reference
VBA Reference:

  • VBA Code Explorer
  • VBA Excel Application
  • VBA Excel Workbook
  • VBA Excel Worksheet
  • VBA Excel Range
  • VBA ActiveX Controls
  • VBA Userforms
  • VBA Projects

Excel Reference:

  • Excel Tutorial
  • Microsoft Excel 2003
  • Microsoft Excel 2007
  • Microsoft Excel 2010
  • Microsoft Excel 2013
  • Microsoft Excel 2016
  • What is a Project?
  • Project Appraisal
  • Project Management
  • Project Plan
  • Project Resource
  • What is Gantt Chart?
  • Excel Templates
  • Excel Project Management Templates
  • PowerPoint Project Management Templates
  • MS Word Project Management Templates
  • Excel Basics Tutorials
  • Excel VBA Tutorials
  • Excel Functions and Formulas

External VBA Reference

Excel VBA ReferenceProject Management Reference
VBA Reference:

  • Getting Started with VBA in Office
  • VBA for MS Office Application: Complete References
  • Excel VBA Reference
  • VBA Tutorials
  • VBA Macros
  • VBA Functions

Excel Reference:

  • Microsoft Excel Reference
  • Excel Formulas
  • Excel 2016
  • Excel Functions
  • Excel Quick Tutorials
  • Project Management Methodologies
  • MS Project Templates
  • Excel, Word, PowerPoint Timeline Templates
  • Excel, Word, PowerPoint Planners and Trackers
  • Microsoft Excel Templates
  • MS PowerPoint Excel Templates
  • MS Word Templates
  • MS Office Schedules Templates
  • Excel, Word, PowerPoint Agenda Templates
  • Excel, Word, PowerPoint Invoices Templates
  • Excel, Word, PowerPoint Business Plans Templates
  • Excel, Word, PowerPoint Financial Management Templates
  • Excel, Word, PowerPoint Minutes Templates

Leave A Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed .

  • VBA Code Explorer
  • Application
    • Methods
      • Calculate
      • CalculateFull
      • FindFile
      • Goto
      • Run
      • Wait
    • Properties
      • Calculation
      • Caption
      • DisplayAlerts
      • DisplayFormulaBar
      • DisplayFullScreen
      • DisplayScrollBars
      • DisplayStatusBar
      • EnableEvents
      • Path
      • ScreenUpdating
      • StatusBar
      • UserName
      • WindowState
  • Workbook
    • Methods
      • Activate
      • Close
      • Protect
      • ProtectSharing
      • RefreshAll
      • RejectAllChanges
      • RemoveUser
      • RunAutoMacros
      • Save
      • SaveAs
      • SaveCopyAs
      • SendMail
      • Unprotect
      • UnProtectSharing
  • Worksheet
    • Methods
      • Activate
      • Calculate
      • Copy
      • Delete
      • Move
      • Protect
      • Select
      • Unprotect
  • Range
    • Methods
      • Activate
      • AddComment
      • AdvancedFilter
      • Calculate
      • Clear
      • ClearComments
      • ClearContents
      • ClearFormats
      • ClearHyperlinks
      • Copy
      • CopyFromRecordset
      • Cut
      • Delete
      • Find
      • Insert
      • ListNames
      • Merge
      • PasteSpecial
  • ActiveX Controls
    • CheckBox
      • Properties
        • Alignment
        • BackColor
        • Caption
        • Enabled
        • Height
        • Left
        • TextAlign
        • Top
        • Width
    • ComboBox
    • CommandButton
    • Image
    • Label
    • ListBox
      • Properties
        • BackColor
        • BorderStyle
        • ColumnCount
        • Enabled
        • Height
        • Left
        • MultiSelect
        • TextAlign
        • Top
        • Width
    • OptionButton
      • Caption
    • TextBox
  • Userform
    • CheckBox
    • ComboBox
    • CommandButton
    • Image
    • Label
    • ListBox
    • OptionButton
    • TextBox
    • Tutorial  Examples
  • Projects
    • Calculator using Excel VBA UserForm
    • Data Entry Userform
    • Excel VBA UserForm: Difference Between Two Dates
    • TOC creator in Excel Workbook using VBA
    • VBA List Folders Subfolders in Directory
    • VBA to Append Data from multiple Excel Worksheets into a Single Sheet – By Column
    • VBA to Consolidate data from multiple Excel Worksheets into a Single Sheet – By Row