Activate Method of Workbook Object VBA

VBA Workbook Activate method will help us to activate a specific Workbook. It is helpful when we have opened multiple workbooks and want to access a particular workbook to manipulate or read some data from the Active Workbook.



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

Why we need to activate a Workbook using VBA?

When we deal with multiple workbooks and if you want to read or write to a specific workbook. You can easily activate a workbook using VBA. And do whatever tasks which you want to do.

VBA Activate Workbook – Syntax

Here is the example syntax to activate a Workbook using VBA. You can use either a Workbook name or Workbook number. When we specify the workbook number if it the order of the workbooks which you are opening.

Workbooks(“Your Workbook Name”).Activate

[Workbook Number]).Activate
‘And you can use Thisworkbook.Activate method to activate the workbook with the current procedure/macro

VBA Activate Workbook – with Name:Example 1

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

'Workbook Activate
Sub sb_Activate_Workbook()
End Sub

VBA Activate Workbook – with Number:Example 2

Please see the below VBA codes to activate a Workbook using workbook number. In this example we are activating a workbook 2 in the currently opened workbooks collection.

'Workbook Activate
Sub sb_Activate_Workbook_Number()
End Sub

VBA Activate Workbook – ThisWorkbook:Example 2

Please see the below VBA codes to activate a currently running macro workbook. This will be very useful while dealing with the multiple workbooks.

Let’s say you have your macros in “MyProjects1.xlsm” and you have opened multiple workbooks say Book2.xlsx, Book3.xlsx, Book4.xlsx, and you can deal with any workbook and come back to your original workbook with the currently running code by just referring to ThisWorkbook Object.

'Workbook Activate
Sub sb_Activate_Workbook_ThisWorkbook()
'Lets say you have written this macro in "MyProjects1.xlsm
'And say you want to write to Book2
Sheet1.Range("A1") = 1
'Now you want to write to Book3
Sheet1.Range("A1") = 1
'You can come back to activate the currently macro running workbook
ThisWorkbook.Activate 'This will activate "MyProjects1.xlsm
End Sub

VBA Activate Workbook – Best Approach

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

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

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

Sub sb_Activate_Workbook_Object()
'Declare the objects here
Dim wbkMain, wbk_A, wbk_B, wbk_C
'Set the Workbooks to Objects
Set wbMain = ThisWorkbook
Set wbk_A = Workbooks("Book2")
Set wbk_B = Workbooks("Book3")
Set wbk_C = Workbooks("Book4")
'Now deal with your workbooks
wbk_A.Sheets(1).Range("A1") = wbkMain.Sheet1.Range("A1")
End Sub

VBA Activate Workbook – Instructions

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

  1. Open an Excel Workbook
  2. Press Alt+F11 to Open VBA Editor
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating a range and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled workbook
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line.

Leave A Comment

Related pages

excel empty cell testchange font color vbaexcel vba messageproject checklist template wordvba run applicationchar excelvlookup definitionadodb connection to access databasesubstitute vba excelhow to sort in ascending order in excelexcel shortcut sheetunprotect excel workbook 2010 with passwordwhere can i find developer tab in excel 2010what is dml in sqlexcel 2007 macro tutorialvba range clearexcel highlight duplicate cellsvba activate worksheetremove dupes in excelwhat is ddl and dmlshortcut key to insert row in excel 2010excel vba active workbookexcel vba worksheetspowerpoint 2007 questions and answerssumif function in excelexcel protect workbookhow to find the duplicate values in excelshortcut keys on excelvba excel password removerexcel consolidate columns into onehow do i check for duplicates in excellookup in ms excelwhat is vlookup function in excelexcel formula explainedexcel vba hide worksheetvba excel gotoexcel coding basicshow to display developer tab in excel 2010exporting xml to exceldim as workbookexcel project portfolio dashboardinterview excel test samplexlnone vbaexcel vba choosemerge multiple worksheets into onehow to delete a header in excelexcel insert cellsexcel loopingexcel macro to remove duplicateshow do i remove hyperlinks in excelexcel resource allocation templatedo while statement vbaaccess 2013 activex controlsvba applicationexcel merge macrogridlines in excel definitionrange function vbahow to delete multiple entries in excelvba create a new worksheetexcel and vba tutorialunlock macro excelexcel tutorial advancedhighlight a column in excelvba code diminsert blank rows in excelunlock excel spreadsheetconsolidate data in excel from multiple worksheetsmicrosoft excel protect sheetvba rowvb listbox add itemchange row height in excel 2010project management timeline template excelinsert function vbavba forms controlsapplication.enableeventsunprotect worksheet excel 2013vba macros tutorialexcel vba with sheetslookup functionsexcel workbook examplestcl in sql serverhow to use pivot tables excel 2010