When you are working with multiple workbooks at the same time, I mean, when you have more than one workbook open at the same time then you need to know the method that can help you activate a workbook that you want to work on.
To activate a workbook using VBA, you need to use the Workbook.Activate method. In this method, you need to specify the workbook name using the Workbook object. It also allows you to use the workbook number instead of the workbook name, but you can only refer to the open workbooks.
In this tutorial, we look at different ways to use this method.
Steps to Activate a Workbook
- Type “Workbooks” to use the workbook object.
- Specify the workbook name in the double quotation marks.
- Enter a dot (.) to get the list of properties and methods.
- Select the Activate method from the list or you can also type it.
- In the end, run the code to activate the workbook.
Sub vba_activate_workbook() Workbooks("Book3").Activate End Sub
Note: If you try to activate a workbook that is not open, VBA will show an error.
Related: Activate a Worksheet using VBA
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Activate a Workbook by using the Number
When you have multiple workbooks open all those workbooks are part of the workbook collection and have a number that you can use to refer to and then you can use the activate method with it. Here’s the code:
Sub vba_activate_workbook() Workbooks(2).Activate End Sub
And if you are trying to activate a workbook using a number that doesn’t exist, VBA will show you an error Run-time error ‘9’ (Subscript out of Range).
You can refer to the workbook where you are writing code by using the ThisWorkbook property. Let’s say you have five workbooks open at the same time but you are working on the “Book1.xlsm”, so when you run the following code, it will activate the “Book1.xlsm”.
Sub vba_activate_workbook() ThisWorkbook.Activate End Sub
Check Before Activating a Workbook
As I said, when you try to activate a workbook that is not opened VBA will show you an error. To deal with this problem the best way is to check for the workbook name first (if it’s open or not) and then activate it.
Sub vba_activate_workbook() Dim wb As Workbook For Each wb In Workbooks If wb.Name = "Book3.xlsx" Then wb.Activate MsgBox "Workbook found and activated" Exit Sub End If Next wb MsgBox "Not found" End Sub
By using the above code, you can specify a workbook name and this will first check for that workbook in all the open workbooks, and if it finds the workbook, it will activate it.
- When you are using the name of the workbook make sure to use the correct file extension
- If you want to activate a workbook that is not yet saved, then you need to use only the name of that workbook without suffixing the file extension.
More on VBA Workbooks
VBA Save Workbook | VBA Close Workbook | VBA Delete Workbook | VBA ThisWorkbook | VBA Rename Workbook | VBA Combine Workbook | VBA Protect Workbook (Unprotect) | VBA Check IF a Workbook is Open | VBA Open Workbook | VBA Check IF an Excel Workbook Exists in a Folder| VBA Create New Workbook (Excel File)