VBA Activate Workbook (Excel File)

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

1. Type “Workbooks” to use the workbook object.
2. Specify the workbook name in the double quotation marks.
3. Enter a dot (.) to get the list of properties and methods.
4. Select the Activate method from the list or you can also type it.
5. 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).

Activate ThisWorkbook

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.

Notes

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