You need to know the technique that can assist you activate 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“).
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
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 accordingly 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 in Excel: 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 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.
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.
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.
Conclusion
In this article, we’ve covered x examples of how to activate another workbook with Excel VBA. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.