Search all worksheets and paste all rows w/search criteria to single sheet

Maczaq - The book was locked for editing and had external links. The code was on your results worksheet rather than a standard module, and I couldn't even step through it because F8 wouldn't work. If I just pushed the button I got an error, probably due to the external links. Frankly, the whole thing looked kind of fishy.

Thanks anyway.

Good morning, maczaq. The macro appears to run perfectly in your sample workbook, but when I import the code, (function included), into my test workbook in Excel 2007 I get runtime errors. The initial ones were "missing object or library", so I set "what" and "r" as variables, but now it stops on "toCopy". I din't know if that is a command 2007 doesn't recognize or what.

Hey, maczaq. The following code seems to work in 2007, with a few glitches. In my test workbook, it doesn always return rows that have the search criteria. I think it is because of the way we are searching. I want the macro to copy the row to the Search worksheet if it finds the word anywhere in the cell. I thin this only returns the results if the search is an exact match to the cell contents.

Please Login or Register  to view this content.

I have attached a cleaned up and simplified version of my Bill Tracking Log, containing the "SearchAndCopy" macro and the "LookingFor" function. You will notice that if you run the macro, and type "CWD" or "Wal Mart" as the search criteria, then it will paste all rows that have a cell matching "CWD" or "Wal Mart" to the Search worksheet. That is good.

However, If I type only part of a cell value as the search criteria, such as "tobacco" or "Bobbie", then it returns no results. Also, it returns no results if I type in a numer or dollar amount, such as $80.00. Ideally, either of these search criteria should paste every row to the "Search" worksheet if it finds those values anywhere in any of the cells on any of the worksheets.

Is this possible?

maczaq - Works beautifully. Thanks!