[Excel VBA] Two Ways to Switch Between Multiple Excel Workbooks

When creating macros that process data across multiple Excel files (workbooks)—such as “copying values from a data workbook to a summary workbook”—you must accurately switch the target workbook being operated on.

There are two main methods in VBA to make a specific workbook active (bring it to the front and make it the target of operations). In this article, I will explain each method and provide guidelines on which one you should use.

Method 1: Operate by Index Number (Order Opened)

Excel manages open workbooks internally in sequential order, such as “the 1st workbook opened,” “the 2nd workbook opened,” and so on. This method specifies the workbook using this index number.

Code and Explanation

Sub ActivateWorkbookByIndex()
    ' Assume 2 or more workbooks are open before running this macro

    ' Activate the 1st workbook opened
    Workbooks(1).Activate
    MsgBox "Activated: " & Workbooks(1).Name
    
    ' Activate the 2nd workbook opened
    ' Workbooks(2).Activate
    ' MsgBox "Activated: " & Workbooks(2).Name
End Sub

• Workbooks: A collection of all currently open workbooks.
• Workbooks(1): Refers to the first workbook that was opened.
• .Activate: The method used to make the specified workbook active.

Caution for this Method

While this method is easy to write, it has a significant flaw: instability. If the person running the macro opens files in a different order than usual, the code may manipulate the wrong workbook, leading to errors or data corruption. You should avoid using this method unless there is a specific reason to do so.

Method 2: Operate by Workbook Name (Recommended)

This method switches the operation target by directly specifying the file name of the workbook. Since it does not depend on the order in which files were opened, it is safe, reliable, and highly recommended.

Code and Explanation

Sub ActivateWorkbookByName()
    ' The full name of the target workbook (including extension)
    Dim targetBookName As String
    targetBookName = "Report_Data.xlsx" ' Example: We want to activate this book

    On Error Resume Next ' Avoid error if the specified workbook is not open
    
    ' Activate the workbook with the specified name
    Workbooks(targetBookName).Activate
    
    ' Reset error handling
    On Error GoTo 0
    
    ' Verify if it was successfully activated
    If ActiveWorkbook.Name = targetBookName Then
        MsgBox "Activated: " & targetBookName
    Else
        MsgBox "The workbook '" & targetBookName & "' is not open or the name is incorrect."
    End If
End Sub

• Workbooks("Filename.ext"): Specify the file name directly as a string. You must include the exact extension, such as .xlsx or .xlsm.
• .Activate: Finds the workbook with the specified name and makes it active.

Advantages of this Method

• Stable Operation: It is not affected by the order in which files are opened.
• Clarity: Reading the code makes it immediately clear which file is being manipulated.

Summary

The fundamental rule when manipulating multiple workbooks in VBA is to clearly specify the target workbook.

• Specifying by Index (Workbooks(1)): Easy to write, but deprecated due to instability.
• Specifying by Name (Workbooks("File.xlsx")): Reliable and easy to understand. Strongly recommended.

When creating macros that handle multiple workbooks, always use the method of specifying the workbook name to ensure processing stability and code readability.