How to ACTIVATE a Workbook using VBA in Excel

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.

workbook-activate

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.

In this tutorial, we look at different ways to use this method.

Steps to Activate a Workbook

type-workbooks
  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.

vba-will-show-an-error

Related: Activate a Worksheet using VBA

Helpful Links: Run a MacroMacro RecorderVisual Basic EditorPersonal 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
activate-a-workbook-by-using-the-number

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

vba-will-show-run-time-error-9

Activate ThisWorkbook

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.

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.