Excel Sheet Open VBA: Open and Activate a Worksheet

To open a worksheet that is already in an Excel workbook, use VBA to activate it: ThisWorkbook.Worksheets("Summary").Activate. If you mean opening a separate Excel file, use Workbooks.Open first, then activate the worksheet inside that workbook. A worksheet is part of a workbook, so VBA activates a sheet rather than opening it as a separate file.

What “open a sheet” means in VBA

Excel distinguishes between a workbook—the file, such as an .xlsx or .xlsm file—and the worksheets or chart sheets contained in it. Opening a workbook loads the file into Excel. Activating a worksheet makes that sheet the current view in its workbook. If the file is already open, you usually only need to activate the desired sheet.

VBA also has a Worksheet object, which lets code refer to a sheet without making it the visible sheet. For example, code can read values from a worksheet in the background. Use .Activate when a person should see the sheet, not merely because your macro needs to work with it.

Names matter: Worksheets("Summary") refers to a worksheet whose tab is named Summary. It does not refer to the sheet’s position, and it is different from the worksheet’s VBA code name. A sheet’s tab name can be changed by a user, so a macro that relies on a particular tab name must be updated if that name changes.

Activate a worksheet in the current workbook

For a worksheet in the same workbook as the macro, ThisWorkbook is usually the clearest reference. It means the workbook containing the VBA code. The following procedure activates a tab named Summary:

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, so a backup gives you a way to recover if code or a later edit changes something unexpectedly.

Option Explicit

Sub ActivateSummarySheet()
    Dim ws As Worksheet

    ' Refer to the sheet by its tab name.
    Set ws = ThisWorkbook.Worksheets("Summary")
    ws.Activate
End Sub

If your tab has another name, replace Summary with that exact name, including spaces and punctuation. For example, a tab called Monthly Report is referenced as ThisWorkbook.Worksheets("Monthly Report"). The sheet name is a text value in quotes; it is not a cell reference.

You can also use ActiveWorkbook, which means the workbook currently in front of the user. That may be useful when a macro is meant to operate on whichever workbook is active, but it can point to the wrong file if the macro activates or opens another workbook along the way. Prefer ThisWorkbook when the target is the file that stores the macro.

Open another workbook, then activate its sheet

To load a separate file from disk, call Workbooks.Open with its full path. Then refer to a worksheet in the returned workbook object. Replace the example path and sheet name with values that match your files:

Save a backup before running this macro, and check that the path points to the intended file. Opening a workbook can trigger its own macros if Excel’s security settings allow them.

Option Explicit

Sub OpenFileAndActivateSheet()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Open the workbook from its full file path.
    Set wb = Workbooks.Open(Filename:="C:\Reports\Annual.xlsx")
    Set ws = wb.Worksheets("Summary")
    ws.Activate
End Sub

The folder and file must exist and be accessible to the current user. If the file is on a shared drive or network location, the drive may need to be connected, and the user may need permission. For a file picker rather than a fixed path, a macro can use Excel’s file-dialog features, but that adds a separate step and should be designed to handle Cancel.

For password-protected workbooks, Excel may prompt for a password unless the macro supplies one through the supported Workbooks.Open arguments. Avoid putting a real password directly into VBA code: workbook code can be inspected, and a stored password may expose the file. Follow your organization’s approved method for handling protected files.

Add and run the VBA code

In Excel for Microsoft 365 on Windows, first save the file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA. Press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, paste a standard macro into the module, and return to Excel. Press Alt+F8, select the procedure, and choose Run. You can also use Developer > Macros.

If the Developer tab is missing on Windows, go to File > Options > Customize Ribbon, select Developer in the main tabs list, and choose OK. On Mac, the Visual Basic Editor and macro commands are available through Excel’s developer or Tools menus, but keyboard shortcuts differ by keyboard and version; use the menu command if a function-key shortcut does not work. Excel for the web can open an .xlsm file, but it cannot run or edit VBA macros. Use the desktop Excel application for these procedures.

Only enable macros in files you trust. Excel may show a security warning for a downloaded or emailed workbook; enabling its macros allows its code to run. A macro that merely activates a sheet is simple, but VBA can also make broader workbook changes, so inspect code and use a copy when learning or testing.

Activate a sheet versus selecting cells

Activating a worksheet changes the visible sheet. Selecting a range changes the selection on that sheet. These are related but distinct actions: a macro can activate a sheet without selecting a particular cell, and it can work with a range without changing what the user sees.

For instance, when the code needs to write a value, it is generally more dependable to qualify the range with its worksheet than to activate the tab and then rely on whichever sheet happens to be active:

Option Explicit

Sub WriteStatus()
    Dim ws As Worksheet

    ' Write directly to the named sheet without selecting a cell.
    Set ws = ThisWorkbook.Worksheets("Summary")
    ws.Range("A1").Value = "Updated"
    ws.Activate
End Sub

This example writes Updated into cell A1 and then displays the Summary sheet. If the user only needs to see the sheet, omit the write line. Avoid using Select as a substitute for a clear object reference; code based on active selections can fail when a different sheet or workbook is in front.

For a worksheet that should open with a particular cell visible, activate the sheet and then select the range. This is mainly a user-interface choice, not a requirement for manipulating data. When a macro processes many sheets, minimizing unnecessary activation and selection generally makes the code easier to follow and less dependent on Excel’s current screen state.

Hidden sheets and protected workbooks

A hidden worksheet cannot be activated while it remains hidden. If you are authorized to display it, make it visible before activation. The workbook’s structure protection may prevent changes to sheet visibility, and the macro will need to account for that restriction rather than assuming it can override protection.

Save a backup before running code that changes visibility or protection-related settings. This example reveals a sheet named Archive and then activates it:

Option Explicit

Sub RevealAndActivateArchive()
    Dim ws As Worksheet

    ' Make the worksheet visible before activating it.
    Set ws = ThisWorkbook.Worksheets("Archive")
    ws.Visible = xlSheetVisible
    ws.Activate
End Sub

Excel supports ordinary hidden sheets and sheets set to xlSheetVeryHidden, which are not shown in the normal Unhide dialog. VBA can change a VeryHidden sheet’s visibility if workbook permissions and protection allow it. Do not treat a hidden sheet as a security boundary: hiding a tab is not a substitute for controlling access to sensitive information.

Sheet protection is different from visibility. A protected sheet can often still be activated, but editing locked cells may be blocked. Workbook structure protection can restrict adding, deleting, moving, hiding, or unhiding sheets. If a macro fails on a protected workbook, confirm which kind of protection is enabled and obtain authorization before changing it.

Common errors and how to fix them

The most common failure is “Subscript out of range.” It usually means VBA cannot find a worksheet or workbook with the name supplied. Check the tab text carefully, including spaces, and confirm that the code is looking in the intended workbook. A worksheet named Data in one workbook is not automatically found through ThisWorkbook if it belongs to another workbook.

Wrong workbook reference: Decide whether the target is the macro’s workbook (ThisWorkbook), the currently active workbook (ActiveWorkbook), or a workbook stored in a variable returned by Workbooks.Open.
Hidden target: Check whether the sheet is hidden. Make it visible, if appropriate and permitted, before calling .Activate.
Wrong kind of tab: Worksheets contains worksheets, while Sheets can also include chart sheets. If the named tab is a chart sheet, a Worksheet variable will not represent it.
File cannot be opened: Verify the complete path, filename extension, access permissions, and whether the file is already moved or renamed. A network location can be unavailable even when its path looks correct.
Macro does not run: Confirm the workbook is saved as .xlsm, that you are using desktop Excel, and that the macro is allowed by the current security policy. Do not enable code from an untrusted source merely to bypass a warning.

During troubleshooting, step through a macro with F8 in the Visual Basic Editor on Windows. This runs one line at a time and can show which reference fails. The Mac editor has equivalent debugging controls, though the exact keyboard shortcuts can differ. Add breakpoints or inspect variable values rather than changing security settings blindly.

Run the action automatically when a workbook opens

If the requirement is to show a particular sheet each time a workbook opens, use the workbook’s Workbook_Open event. This event procedure belongs in the ThisWorkbook code module, not in a standard module. In the Visual Basic Editor, open the workbook’s ThisWorkbook object and add an event procedure there.

Keep a backup before adding or testing an automatic macro. The event runs whenever the workbook opens and macros are enabled, so a mistaken sheet name can cause an error each time. This example assumes the workbook contains a visible worksheet called Summary:

Option Explicit

Private Sub Workbook_Open()
    Dim ws As Worksheet

    ' Show the starting worksheet when this workbook opens.
    Set ws = ThisWorkbook.Worksheets("Summary")
    ws.Activate
End Sub

Excel’s macro security settings affect whether the event runs. If macros are disabled, the workbook can still open, but VBA does not execute. The event should not be used to surprise users or bypass their security choices. If the workbook is shared, tell users what the startup macro does and provide a clear way to use the file without enabling macros when practical.

Frequently Asked Questions

How do I open Sheet1 using VBA?
For a worksheet in the workbook containing the code, use ThisWorkbook.Worksheets("Sheet1").Activate. The tab name must match exactly, and the sheet must be visible.

What is the difference between Activate and Select?
Activate makes a worksheet the active sheet. Select can select a sheet or a range, but selecting is usually unnecessary for code that reads or writes cells. Use worksheet-qualified references for more reliable data operations.

Can VBA open a worksheet without opening the workbook?
No. A worksheet is contained in a workbook. The workbook must be open before VBA can activate one of its sheets. Use Workbooks.Open to open a file that is not already loaded.

Why does VBA say Subscript out of range?
Usually, the workbook reference or sheet name does not match an open workbook and its tabs. Check spelling, spaces, and whether you used ThisWorkbook when the sheet is actually in another file.

Can Excel for the web run a VBA macro?
No. Excel for the web does not run or edit VBA. Open the workbook in desktop Excel to use VBA, and make sure the workbook format and macro security settings allow the code to run.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.