How to Search Every Excel Worksheet for Multiple Invoice Numbers using VBA
Question details
The user wants to locate multiple specific invoice numbers across all worksheets in a workbook and extract the matching rows into a new workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific invoice records scattered across multiple sheets based on a provided list of reference numbers without manual filtering.
- Observed behavior
- Needs an automated VBA solution or efficient alternative to locate the data, copy entire rows of matched records, and paste them into a centralized destination file.
Ensure that the Developer tab is enabled in your ribbon and that macros are allowed in your Trust Center settings. Always save a backup copy of your workbook before running VBA scripts that manipulate or copy bulk data.
Use a VBA Macro to Loop Through Worksheets and Search
Create a VBA script that iterates through all worksheets, searches for each invoice number using the Find function, and copies matching rows to a new file.
This method is highly effective for dynamically locating data. Instead of hard-coding dozens of invoice numbers into the script, it is best practice to store your target invoice numbers in a designated worksheet range or array so the macro can easily loop through them.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) Editor. In the Project Explorer pane on the left, right-click your workbook name, select 'Insert', and click 'Module'.
Create a new worksheet named 'InvoiceList' and paste your required invoice numbers into Column A. In your VBA module, write code to store this column's data into an array variable.
Create a 'For Each' loop to iterate through every worksheet in 'ActiveWorkbook.Worksheets'. Inside this loop, use the 'Range.Find' method to search the specific column containing invoice numbers for each value in your array. Ensure 'LookAt:=xlWhole' is used for exact matches.
When a match is found ('If Not rng is Nothing'), use 'rng.EntireRow.Copy' and paste the data into the next available empty row of a newly created workbook ('Workbooks.Add'). Run the macro by pressing 'F5'.
Consolidate Worksheets and Filter using Power Query
For massive datasets or hundreds of invoices, consolidating the data first and using Power Query is often more efficient than running a VBA loop.
Extract and Manage Data Efficiently with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly run scripts to search across multiple sheets and extract invoice data exactly like you would in Microsoft Excel.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your scattered invoice data.
- 2. Access the Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click on 'Developer' to access the macro environment.
- 3. Run the Search Macro: Click 'Macros' or open the VBA editor to input your search script, then run it to extract matching rows instantly.
Frequently Asked Questions
How do I store a long list of invoice numbers for the VBA search?
Instead of hard-coding them directly into your VBA script, enter the invoice numbers in a dedicated column on a new worksheet (e.g., 'InvoiceList'). You can modify the VBA code to read this specific range into an array and loop through it dynamically.
Will the VBA macro automatically copy the header row to the new workbook?
Not automatically. You will need to explicitly code the macro to copy the header row from your source sheet and paste it into row 1 of the new destination workbook before the main data search loop begins.
Why does the Find method in VBA miss some matching invoices?
Ensure the 'LookAt' parameter in your 'Find' method is set to 'xlWhole' rather than 'xlPart'. Setting it to 'xlPart' might cause false positives or skip exact matches by finding partial strings (e.g., finding '123' inside '12345').
Can I use formulas instead of VBA to extract this invoice data?
While possible using complex lookup arrays (like INDEX/MATCH combined with FILTER), formulas become extremely resource-intensive and convoluted when searching across dozens of sheets simultaneously. VBA or Power Query are much more reliable for multi-sheet extraction.
