VBA Activate Workbook - ActiveWorkbook

Maybe your like

Skip to content Toggle Navigation

120+ Professional Project Management Templates!

Save Up to 85% LIMITED TIME OFFER

View Details Analysistabs Logo

Search for: Go to... Home » VBA Code Explorer » Workbook » Methods » ActivateVBA Activate Workbook - ActiveWorkbook

Share This Post

In This Article

Why we need to activate a Workbook using VBA?
VBA Activate Workbook – Syntax
VBA Activate Workbook – with Name:Example 1
VBA Activate Workbook – with Number:Example 2
VBA Activate Workbook – ThisWorkbook:Example 2
VBA ActiveWorkbook Object
Set ActiveWorkbook in Excel VBA
VBA ThisWorkbook Object in Excel VBA
Set ThisWorkbook in Excel VBA
VBA Activate Workbook – Best Approach
VBA Activate Workbook – Instructions

VBA Code Library

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
Excel VBA Range
- Methods
  - Activate
  - AddComment
  - AdvancedFilter
  - Calculate
  - Clear
  - ClearComments
  - ClearContents
  - ClearFormats
  - ClearHyperlinks
  - Copy
  - CopyFromRecordset
  - Cut
  - Delete
  - Excel VBA PasteSpecial
  - Find
  - Insert
  - ListNames
  - Merge
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

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

View Details

120+ PROFESSIONAL

Project Management Templates

View Details

120+ PM Templates Includes:

50+ Excel Templates
50+ PowerPoint Templates
25+ Word Templates

Effortlessly Manage Your Projects

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

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.

Activate Method Excel Workbook Object

In this topic:

Why we need to activate a Workbook using VBA?
VBA Activate Workbook – Syntax
VBA Activate Workbook – with Name: Example 1
VBA Activate Workbook – with Number: Example 2
VBA Activate Workbook – ThisWorkbook: Example 3
VBA ActiveWorkbook Object
Set ActiveWorkbook in Excel VBA
VBA ThisWorkbook Object in Excel
Set ThisWorkbook in Excel VBA
VBA Activate Workbook – Best Approach
VBA Activate Workbook – Instructions

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 ‘Or Workbooks([Workbook Number]).Activate ‘And you can use Thisworkbook.Activate method to activate the workbook with the current procedure/macro ThisWorkbook.Activate

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() Workbooks("Project1").Activate 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() Workbooks(2).Activate 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 Workbooks("Book2").Activate Sheet1.Range("A1") = 1 'Now you want to write to Book3 Workbooks("Book3").Activate 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 ActiveWorkbook Object

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

ActiveWorkbook.ActiveSheet.Range("A1")="Some Value" 'OR ActiveSheet.Range("A1")="Some Value" 'OR Range("A1")="Some Value"

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

Set ActiveWorkbook in Excel VBA

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

Sub sbSetActiveWorkbookVBA() Dim wb As Workbook Set wb = ActiveWorkbook Set ws = wb.ActiveSheet ws.Range("A1") = "Some Value" End Sub

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

VBA ThisWorkbook Object in Excel VBA

While automating the Excel Tasks, we usually have all our macros in one Excel Workbook (say Book1.xlsm) and deal with with multiple workbboks and files. We activate different workbooks to access the file objects and do some activity. For example, we activate Book12.xlsx to enter some data, we may open and format some ranges. While accessing the different objects in different workbooks, we generally activate the Workbook and do perform some operation with Excel VBA Macros.

What if you need to wok on the same workbook(Book1.xlsm) where all your macros are written. You do not required to activate and use VBA ActiveWorokbook object. Instead, you can use VBA Thisworkbook object to deal with your macro file.

Here is the simple macro to insert some values in ThisWorkbook using VBA.

ThisWorkbook.Sheets(1).Range("A1") = "Some Value"

Set ThisWorkbook in Excel VBA

We can also set ThisWorkbook to a variable using VBA and access it whereever it is required. For example the following macro will do print the some value at A1 of Sheet 1 of ThisWorkbook.

Sub sbThisWorkbookVBA() Dim wb As Workbook Set wb = ThisWorkbook Set ws = wb.Sheets(1) ws.Range("A1") = "Some Value" 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:

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

Effortlessly Manage Your Projects and Resources120+ 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

Browse All Templates Project Management Templates

All-in-One Pack 120+ Project Management Templates

120+ PM Templates

Essential Pack 50+ PM Templates

50+ PM Templates

Excel Pack 50+ Excel PM Templates

50+ Excel Templates

PowerPoint Pack 50+ Excel PM Templates

50+ PPT Templates

MS Word Pack 25+ Word PM Templates

25+ Word Templates

Ultimate Project Management Template

Ultimate PM Template

Ultimate Resource Management Template

Resource Management

Project Portfolio Management Templates

PPM TemplatesPublished On: March 7, 2015Last Updated: March 2, 2023

About the Author: PNRao

Hi, I’m PNRao—an Excel & VBA developer with 20 years in data mining, automation, and project management. Day-to-day I turn raw data into clear insight, replace repetitive work with one-click workflows, and guide teams with smarter project management. On Analysistabs.com I share battle-tested tips on Excel, VBA, SQL, Automation, Project Management, and Data Analysis—plus a growing library of free and premium Project Management Templates. My goal is to help you work faster, build sharper tools, and level up your career. Let's master data and manage projects effectively, together.

3 Comments

Lisa April 27, 2019 at 12:34 AM - Reply
Hello,

How do you activate a workbook whose full name changes because it is downloaded from the internet and gets a number appended to each file? In this case, you would not always know the full name in order to activate it. How do you call this workbook? I am using

Workbooks(ActiveWorkbookName).Activate

but some users are getting a run-time error 1004 “Application-defined or Object-defined error”
Neha.Meghani May 7, 2020 at 6:39 AM - Reply
Hi,

Can you please help me with code how to consolidate different workbooks into one workbook.
M Jairam August 13, 2020 at 10:14 AM - Reply
I used the coding to Activate workbooks Best Approach..using workbook objects . I created 4 work books.Myprojects,Books2,3,4 as per your recommendation.It did not work ..Run-time error Subscript out of range.. ***** Can you help me in this….Thanks..