When working with multiple workbooks in Excel VBA, activating or switching focus to the right workbook is crucial. Activating a workbook brings it to the front of the screen and makes it active.
This activation helps ensure that your code operates on the intended workbook.
Activating workbooks can help you specify the source and destination for operations like copying and pasting data between workbooks. For instance, you can activate the workbook containing the data you want to copy, then activate the destination workbook and paste it into it.
We use the ‘Activate’ method of the ‘Workbooks’ collection to activate a workbook. The ‘Workbooks’ collection is a built-in object that represents all open Excel workbooks in a specific instance of Excel.
In this tutorial, I show you five examples of how to activate a workbook using VBA.
Example #1: Activate a Workbook Using its Full Name
You can activate a workbook by specifying its full name if it’s open.
The following example code activates the workbook “2023 Sales Report.xlsm” using its full name:
Sub Example_1() ' Declare workbook variable Dim workbookName As String ' Set workbook name workbookName = "2023 Sales Report.xlsm" ' Activate workbook Workbooks(workbookName).Activate ' Work with Sheet1 With Workbooks(workbookName).Sheets("Sheet1") ' Activate Sheet1 .Activate ' Set A1 value to 200 .Range("A1").Value = 200 End With End Sub
This code activates the “2023 Sales Report.xlsm” workbook, activates “Sheet1” within that workbook, and sets the value of cell A1 to 200.
It assumes that the workbook with the specified name exists and is open in the Excel application.
You can use the following alternative code that utilizes the “Name” property of the workbook object:
Sub Example_1_Alternative() ' Declare workbook object Dim wb As Workbook ' Loop through open workbooks For Each wb In Workbooks ' Check if workbook name matches If wb.Name = "2023 Sales Report.xlsm" Then ' Activate workbook wb.Activate ' Show message MsgBox "Workbook found and activated." ' Exit Sub Exit Sub End If ' Next workbook Next wb ' Show not found message MsgBox "Workbook not found." End Sub
The code searches all open workbooks to find “2023 Sales Report. xlsm”.
If the workbook is found, it is activated, and a message stating, “Workbook found and activated” is displayed. If the workbook is not found, a different message reads, “Workbook not found.”
Also read: VBA Run Macro from Another Workbook or Macro
Example #2: Activate a Workbook Using its Partial Name
To activate a workbook using its partial name (a substring of its full name), you can use a VBA macro that searches through all open workbooks and activates the one that contains the partial name.
Here’s a sample VBA code to do this:
Sub Example_2() ' Declare variables Dim partialName As String Dim wb As Workbook Dim foundWorkbook As Boolean ' Set partial name to search partialName = "Tutorials" ' Loop through all open workbooks For Each wb In Workbooks ' Check if partial name is in workbook name If InStr(1, wb.Name, partialName, vbTextCompare) > 0 Then ' Activate workbook wb.Activate ' Set flag to True foundWorkbook = True ' Show message MsgBox "Workbook found and activated." ' Exit loop Exit For End If ' End loop Next wb ' Check if workbook was not found If Not foundWorkbook Then ' Show not found message MsgBox "Workbook with partial name '" & partialName & "' not found." End If End Sub
searches for a workbook whose name contains a specific partial name (“Tutorials” in this case) among all open workbooks.
If it finds such a workbook, it activates it and shows a message box stating “Workbook found and activated.”
If it doesn’t find any workbook with the partial name, a message box saying “Workbook with partial name ‘Tutorials’ not found” is displayed. The subroutine uses a flag variable foundWorkbook to keep track of whether a matching workbook was found.
Note: The ‘InStr’ function performs a case-insensitive search for the “partialName.”
Example #3: Activate a Workbook Using an Object Variable
You can activate a workbook by assigning it to an object variable and using the variable to activate it. See the code example below:
Sub Example_3() ' Declare workbook object Dim wb As Workbook ' Ignore errors temporarily On Error Resume Next ' Try to set wb to the specific workbook Set wb = Workbooks("Sales Report.xlsm") ' Reset error handling On Error GoTo 0 ' Check if wb is set If Not wb Is Nothing Then ' Activate workbook wb.Activate Else ' Show message and exit MsgBox "Workbook is not open." Exit Sub End If ' Activate Sheet1 and set A1 to 55 wb.Sheets("Sheet1").Activate wb.Sheets("Sheet1").Range("A1").Value = 55 End Sub
The code checks whether the “Sales Report.xlsm” workbook is open.
If it is, it will activate the workbook and then activate “Sheet1” to set the value of cell A1 as 55.
However, if it is not open, it will display a message saying “Workbook is not open” and exit the subroutine.
Also read: VBA Check If Workbook Is Open
Example #4: Activate a Workbook Using its Index Number
You can activate a workbook in the ‘Workbooks’ collection using its index position. The index numbers represent the order in which workbooks were opened or created during the current Excel session.
The index starts at 1 for the first open workbook and increments with each additional workbook.
The below code activates the workbook with index number 2 in the ‘Workbooks’ collection:
Sub Example_4() ' Declare variables Dim wbIndex As Integer Dim wb As Workbook ' Set workbook index wbIndex = 2 ' Ignore errors temporarily On Error Resume Next ' Try to set wb to the workbook at index Set wb = Workbooks(wbIndex) ' Reset error handling On Error GoTo 0 ' Check if wb is set If Not wb Is Nothing Then ' Activate workbook wb.Activate Else ' Show message and exit MsgBox "Workbook with index " & wbIndex & " does not exist." Exit Sub End If ' Activate Sheet1 and set A1 to 75 wb.Sheets("Sheet1").Activate wb.Sheets("Sheet1").Range("A1").Value = 75 End Sub
The code example checks if the workbook with index number 2 exists, and if it is, it activates the workbook and then activates “Sheet1” to set the value of cell A1 to 75.
If the workbook is not found, it displays the message “Workbook with index 2 does not exist” and exits the subroutine.
Also read: VBA Protect Excel Workbook
Example #5: Activate a Workbook Using the “Like” Operator
You can use the “Like” operator in VBA to activate a workbook whose name matches a specific pattern. The “Like” operator allows you to use wildcard characters such as asterisk (*) to perform pattern matching.
Here’s an example of a VBA code that activates a workbook with a name that matches a specific pattern:
Sub Example_5() ' Declare a Workbook object variable to store the workbook Dim wb As Workbook ' Declare a String variable to hold the pattern we are looking for in the workbook name Dim wbNamePattern As String ' Initialize the name pattern to search for (in this case, any workbook name starting with "2023" and ending in ".xlsm") wbNamePattern = "2023*.xlsm" ' Loop through each workbook in the Workbooks collection For Each wb In Workbooks ' Check if the workbook name matches the pattern If wb.Name Like wbNamePattern Then ' Activate the matching workbook wb.Activate ' Activate the Sheet1 in the matching workbook wb.Sheets("Sheet1").Activate ' Set the value of cell A1 in Sheet1 to 105 wb.Sheets("Sheet1").Range("A1").Value = 105 ' Exit the For loop since we found a match Exit For End If ' Move on to the next workbook Next wb ' Check if the Workbook object is Nothing, which means no match was found If wb Is Nothing Then ' Show a message box saying no matching workbook was found MsgBox "No matching workbook found." End If End Sub
In the code above:
- ‘wbNamePattern’ is the pattern used to match workbooks. For this example, it’s set to “2023*.xlsm,” which matches workbooks starting with “2023” and ending with “.xlsm.”
- The code then loops through all open workbooks using a “For Each” loop.
- For each workbook, it checks if the name matches the specified pattern using the “Like” operator.
- If a matching workbook is found, it activates it and then activates “Sheet1” to set the value of cell A1 to 105 and exits the loop.
- If no matching workbook is found, a message box displays “No matching workbook found.”
In this tutorial, we walked through five different code examples showing how you can use VBA to activate workbooks in Excel.
Activating workbooks is a common task when automating things in Excel with VBA. I tried to provide a nice mix of examples so you can see different techniques for activating workbooks depending on your needs.
Let me know if any part was confusing or if you have any other questions!
Other Excel articles you may also like: