How to Activate Workbook Using VBA

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

Also read: VBA to Copy Range to Email Body

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:

  1. ‘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.”
  2. The code then loops through all open workbooks using a “For Each” loop.
  3. For each workbook, it checks if the name matches the specified pattern using the “Like” operator.
  4. 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.
  5. 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!

Happy coding!

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment