Activate Another Workbook with VBA in Excel (7 Examples)

You need to know the technique that can assist you in activating a workbook that you wish to work on while you are working with numerous workbooks at once, by which I mean when you have more than one workbook open at once. That is why, we will discuss how to activate another workbook by using VBA in Excel. In the above overview video, there are 3 open workbooks: “Activate Another Workbook,” “Book1,” and “Book2.” The workbook that is now open is “Book1“. The “Activate Another Workbook” contains the macro example. When the macro is run, Excel activates the workbook containing the macro example (“Activate Another Workbook“).

How to Open VBA Macro Editor in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to perform Vlookup with multiple criteria in Excel. Therefore, you can follow the simple steps to open the VBA editor.

Steps:

• Firstly, we will open the Developer tab.
• Then, we will select the Visual Basic command.

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write the VBA code.

Activate Another Workbook with VBA: 6 Suitable Examples

This article will demonstrate how to activate another workbook in  Excel VBA by giving 7 practical examples with explanations. Here, we will activate another workbook by using Filename, object variable, partial Name with the Instr Function, Like operator, and so on. Now, you can go through a sample video of our work on how to activate another workbook in Excel VBA.

Example 1: Activate Another Workbook by Workbook Name in Excel VBA

This example will help you to activate another workbook by closing your existing workbook when you apply this VBA Macro by putting your filename.

• Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Activate_AnotherWorkbook_Using_FileName()
    'Activating the workbook by its name
    Workbooks("Activate Another Workbook.xlsm").Activate
End Sub

Now save your Excel file and Run the code. You will see the following:

Now, for a better understanding, you can go through the whole process and see the output video link in the above section where we have included one as all the output videos are the same.

Example 2: Using Object Variable to Activate Another Workbook in Excel VBA

Here, you will get a clear concept of activating a workbook by using a simple macro with an object variable.

• Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Activate_AnotherWorkbook_With_ObjectVariable()
    'Declaring variable
    Dim ObjectVar As Workbook
    'Assigning the workbook filename to variable
    Set ObjectVar = Workbooks("Activate Another Workbook.xlsm")
    'Activating the workbook
    ObjectVar.Activate
End Sub

Now, for a better understanding, you can go through the whole process and see the output video link in the above section where we have included one as all the output videos are the same.

Example 3: Activate Another Workbook by Number in Excel VBA

Here, we will use a specific workbook number to activate this particular workbook with the help of a simple macro.

• Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Activate_AnotherWorkbook_ByNumber()
    'Using Workbook number to activate
    Workbooks(1).Activate
End Sub

Now, for a better understanding, you can go through the whole process and see the output video link in the above section where we have included one as all the output videos are the same.

Example 4: Activate Another Workbook with a Specific Worksheet in Excel VBA

Now, we will demonstrate an example where we will activate a workbook with a specific sheet by using a very convenient way.

• Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Activate_AnotherWorkbookAndWorksheet()
    'Activating the particular workbook with particular worksheet
    Workbooks("Activate Another Workbook.xlsm").Worksheets("Employee_Information").Activate
End Sub

Now, for a better understanding, you can go through the whole process and see the output video link in the above section where we have included one as all the output videos are the same.

Example 5: Using InStr Function to Activate Another Workbook with Partial Filename in Excel VBA

In this section, you will see that we will activate another workbook by closing the existing workbook with the help of a Macro where we will apply the VBA InStr function and put the partial filename in the code.

• Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Activate_AnotherWorkbook_With_PartialFilename_Using_InStrFunction()
    'Declaring variable
    Dim Myworkbook As Workbook
    'Applying For Each loop to loop through all the workbooks
    For Each Myworkbook In Application.Workbooks
        'Using If statement to activate our desired workbook by looping through all the workbooks
        If InStr(1, Myworkbook.Name, "Activate Another", vbBinaryCompare) > 0 Then Myworkbook.Activate
    Next Myworkbook
End Sub

Therefore, for a better understanding, you can go through the whole process and see the output video link in the above section where we have included one, as all the output videos are the same.

Read More: Excel VBA to Activate Workbook with Partial Name

Example 6: Using Like Operator to Activate Another Workbook in Excel VBA

Here, we will apply the Like operator in the VBA code to activate another workbook in Excel.

• Now, copy the following code and paste it into the above Module. After that, click on Run to see the output.

Sub Activate_AnotherWorkbook_Using_LikeOperator()
    'Declaring variable
    Dim Myworkbook As Workbook
    'Using For Each loop to loop through all the workbooks
    For Each Myworkbook In Application.Workbooks
        'Using If condition with Like operator to activate the particular workbook
        If Myworkbook.Name Like "Activate Another Workbook*.xlsm" Then Myworkbook.Activate
    Next Myworkbook    
End Sub

Finally, for a better understanding, you can go through the whole process and see the output video link in the above section where we have included one as all the output videos are the same.

Read More: How to Apply VBA to Open and Activate Excel Workbook

Frequently Asked Questions

1. If I don’t see another workbook mentioned in the “Switch Windows” menu, how can I activate it?

The workbook you wish to activate might be minimized or hidden behind another window if it isn’t listed in the “Switch Windows” menu. You can either use the keyboard shortcut “Alt + F6” to cycle among the open Excel windows or try clicking on the Excel icon in the taskbar.

2. Is there a faster way in Excel to transition between open workbooks?

To swiftly switch between open worksheets in Excel, press “Ctrl + Tab” on your keyboard. Each workbook will be cycled through in the order they were opened. The “Ctrl + Shift + Tab” keyboard shortcut can also be used to cycle through workbooks in reverse.

3. Can I activate multiple workbooks simultaneously?

By selecting each workbook you want to activate in the “Switch Windows” option while holding down the “Ctrl” key, you can activate multiple workbooks at once. You can work with all of the selected workbooks at once because they will all be engaged.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it yourself.

Conclusion

In this article, we’ve covered 6 examples of how to activate another workbook with Excel VBA. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.