VBA Activate Workbook (Excel File)

Contents hide Steps to Activate a Workbook Activate a Workbook by using the Number Activate ThisWorkbook Check Before Activating a Workbook Notes Related Tutorials

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.

What is VBA

Related Tutorials

• Copy an Excel File (Workbook) using VBA
• VBA Close Workbook (Excel File)
• VBA Combine Workbooks (Excel Files)
• VBA Create New Workbook (Excel File)
• VBA Delete Workbook (Excel File)
• VBA Open Workbook (Excel File)
• VBA Protect/Unprotect Workbook (Excel File)
• VBA Rename Workbook (Excel File)
• VBA Save Workbook (Excel File)

• VBA ThisWorkbook (Current Excel File)
• VBA Workbook – A Guide to Work with Workbooks in VBA
• VBA Check IF a Workbook Exists in a Folder (Excel File)
• VBA Check IF a Workbook is Open (Excel File)
• Save an Excel Macro-Enabled Workbook (.xlsm File Type)