VBA & Macro Problems

Fix Excel VBA Cannot Find Worksheet with ThisWorkbook.Sheets

John Wilson Sep 27, 2026 868 views

Question details

The user is experiencing an issue where an Excel VBA macro fails to find a specific worksheet when using the ThisWorkbook.Sheets reference.

How to Fix Excel VBA Cannot Find a Worksheet with ThisWorkbook.Sheets

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a VBA macro stored in a personal macro workbook or add-in that attempts to modify or access a sheet in the currently displayed workbook.
Observed behavior: The VBA code throws an error or returns unexpected results because ThisWorkbook targets the file where the code resides, rather than the active workbook containing the intended worksheet.

Before you start

Verify that your target workbook is currently open and active, and check the exact spelling of your worksheet name (e.g., 'Planning') to ensure there are no hidden trailing spaces.

Solution 1Recommended

Replace ThisWorkbook with ActiveWorkbook

Use this solution when your macro is stored in an add-in or Personal Macro Workbook but needs to interact with the spreadsheet you are currently viewing.

The command ThisWorkbook always refers to the specific workbook file where the VBA code is written. If your code lives in a separate add-in file, it will search for the worksheet inside that add-in. ActiveWorkbook, however, refers to the workbook currently in focus.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor.

Locate Your Code

In the Project Explorer pane on the left, find the module containing your problematic code and double-click to open it.

Modify the Reference

Find the line of code that reads similar to Set planningSheet = ThisWorkbook.Sheets("Planning") and change ThisWorkbook to ActiveWorkbook.

Run the Macro

Save your code changes and execute the macro again while your target spreadsheet is active on the screen.

Replace ThisWorkbook with ActiveWorkbook

Reference Updated: Your macro will now correctly target the worksheet in the currently active file instead of the background macro file.

Solution 2

Reference the Target Workbook by Name

Use this method if multiple workbooks are open and relying on ActiveWorkbook might accidentally target the wrong file.

Solution 3

Verify Worksheet Naming and Syntax

Ensure the worksheet name exactly matches the VBA string, as hidden characters or typos will cause a reference failure.

Manage Macros Easily

Use WPS Spreadsheet to Run and Edit VBA Macros

WPS Office provides excellent support for Excel macro-enabled workbooks (.xlsm). You can seamlessly open, edit, and troubleshoot your VBA code using the built-in VBA editor in WPS Spreadsheet.

1. Install WPS Office: Download and install WPS Office on your computer.
2. Open Your Macro File: Open your macro-enabled workbook (.xlsm) in WPS Spreadsheet.
3. Access Developer Tools: Navigate to the Developer tab on the top ribbon.
4. Edit the VBA Code: Click on 'Visual Basic' or 'Macros' to access the editor and update your workbook references.

Fully compatible with Microsoft Excel macro formats (.xlsm, .xlsb)Built-in developer tools and VBA editor to quickly fix reference errorsLightweight, fast, and free to download

microsoft office alternative - wps office

Frequently Asked Questions

What is the main difference between ThisWorkbook and ActiveWorkbook in Excel VBA?

ThisWorkbook refers specifically to the workbook file where the VBA code currently resides. ActiveWorkbook refers to the workbook that currently has focus in the Excel window, regardless of where the macro code is stored.

Why does my code work when placed in the sheet module but fails in a personal add-in?

When placed in a sheet module, ThisWorkbook accurately points to the file containing the intended sheet. If moved to a personal add-in, ThisWorkbook points to the add-in file itself, which does not contain your target worksheet, resulting in a reference error.

How can I debug a 'Subscript out of range' error when referencing sheets?

This error usually indicates that the sheet name does not exist in the referenced workbook. Verify that you are referencing the correct workbook (using ActiveWorkbook or Workbooks("Name")) and that the sheet name in the code exactly matches the tab name, including any spaces.

Excel & Data Problems