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 workbooks that are open.
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
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 the 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 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 Tutorials on Working with Workbooks in VBA
- How to Insert a New Workbook using VBA
- How to Save (and Save As) a Workbook using a VBA
- How to Close a Workbook using a VBA
- How to Delete a Workbook using VBA
- How to use to ThisWorkbook in VBA
- How to Change Name of a Workbook using VBA
- How to Combine Workbooks using VBA
- How to Protect\Unprotect a Workbook using VBA in Excel
- How to Check IF a Workbook is OPEN using VBA in Excel
- How to Open a Workbook using VBA
- How to Check IF an Excel File Exists in a Folder