VBA & Macro Problems

How to Use Excel VBA to Search Multiple Worksheets and Perform Lookups

Kushani Nimanthika Oct 23, 2026 868 views

Question details

The user needs an Excel VBA macro to search across several worksheets for a unique identifier (such as a Sales Person or PSID) and perform a lookup on the worksheet that contains the matching value.

Use Excel VBA to Search Multiple Worksheets and Perform a Lookup

Product: Excel
Device & OS: not provided
Scenario: Managing a workbook where records are distributed across multiple sheets, requiring an automated way to locate a specific record and extract associated data.
Observed behavior: When attempting to script this process, users often encounter errors if the search value is not found or if a text string is improperly passed as the lookup table argument.

Before you start

Ensure you have the Developer tools enabled in your spreadsheet application and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA code execution.

Solution 1Recommended

Consolidate Worksheets into a Single Table

The simplest and most reliable approach is to merge the data into a single master table before performing any lookups, removing the need for complex VBA loops.

Instead of writing complex macros to iterate through multiple sheets, combining your data into one continuous list drastically reduces calculation times and eliminates macro runtime errors.

Combine worksheet data

Copy all records from your individual worksheets and paste them into a single master worksheet.

Standardize headers

Ensure the merged table has consistent column headers, especially for the unique identifier column (e.g., PSID or Sales Person).

Use standard lookup functions

Write a standard VLOOKUP or XLOOKUP formula pointing directly to the master table instead of using VBA to search across tabs.

Consolidate Worksheets into a Single Table

Simplified Data Management: Consolidating sheets not only makes lookups easier but also ensures your workbook is optimized for faster data filtering and pivot table creation.

Solution 2

Use VBA Find Method with Range Objects

If merging sheets is not possible, use VBA to loop through worksheets, handle the Find method properly, and execute the VLOOKUP using valid Range objects.

Advanced Spreadsheet Management

Use WPS Spreadsheet for Powerful VBA Macro Support

WPS Office provides robust built-in support for VBA and Macros, allowing you to run, edit, and troubleshoot complex multi-sheet lookup scripts seamlessly.

1. Open your macro workbook: Download and install WPS Office, then open your .xlsm file in WPS Spreadsheet.
2. Access the VBA Editor: Go to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA Editor.
3. Insert your script: Insert or edit your multi-sheet search and lookup macro code within the appropriate module.
4. Run the macro: Run the macro directly to locate your identifiers across multiple worksheets and instantly perform lookups.

Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for creating and debugging multi-sheet lookup macrosLightweight application that handles large datasets and loops smoothlyFree to download with a highly intuitive user interface

Frequently Asked Questions

Why does my VBA Find method return a runtime error?

If the searched value does not exist, the Find method returns 'Nothing'. Trying to access a property like '.Row' on 'Nothing' causes a runtime error. Always use 'If Not FoundCell Is Nothing Then' to verify the result before accessing properties.

Can I use a text string for the VLOOKUP table array in VBA?

No, passing a text string (like "Sheet2!A1:D100") as the table array in Application.WorksheetFunction.VLookup will cause a type mismatch error. You must pass a valid Range object, such as ws.Range("A1:D100").

Is there a formula alternative to VBA for searching multiple sheets?

Yes, you can nest IFERROR and VLOOKUP functions to search through multiple sheets sequentially. Alternatively, using INDIRECT allows you to build dynamic sheet references, though standardizing data into one sheet is usually more efficient.

Excel & Data Problems