Activate Method of Worksheet Object VBA

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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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

Leave A Comment


Related pages


how to insert command button in excelcombine data from multiple workbooksexcel vba listbox rowsourceexcel vba create folderunprotect sheet excel 2010definition of hlookup in excelexcel 2007 unprotect workbookexcel macro enableunhide column in excelproject schedule gantt chart excel templatemerge columns in excel 2007powerpoint project management templatethe macros in this project are disabled excel 2007message box vba accessbubble chart template exceladvanced filter in vbastacked line graph excelinsert button exceldml querieshow does the vlookup function workvba datesformula for vlookup in excelbasic macro in exceltick box excelhow to use trim function in excelexcel look upautofit row height excel 2010microsoft word macros examplesmultiple if functions in excel 2010how to do an hlookupvba end loopexcel unhide sheethow to open xml file in excel 2007comment excel vbaprotect cells in excel 2007excel delete empty rowsdelete empty cellsclearcontents vbaword templates agendafilter macro excelcopy and paste macro in excelhow to unhide everything in excelvba cell selectionexcel vba pastespecialvba for loopinsert a row in exceldml sql commandsadvanced vba tutorialopen xlsx file in excel 2003dml commands in sql with examples pdfexcel programme templateexcel tutorial beginnerhot to merge cells in excelvba access excelvlookup for idiotsvba copy to new workbookcell width in excelpivot chart in excel 2007instr function in excelhlookup tutorialunlock vba projectvbscript to delete filescreate userform in excel 2007how to protect a excel sheet with passwordmicrosoft excel intermediate tutorialwhere is chart wizard in excel 2010excel macro data validationhow to use the sumif function in microsoft excel 2010data definition language commandsdata manipulation language dmlbasic excel shortcutsexcel macro coding tutorial