Searching across all sheets

[JoeSpareBedroom]

Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

 

[John Bundy]

This is a good discussion on some issues with find and replace across many or
single sheets.
http://www.vbaexpress.com/forum/showthread.php?t=11444

 

[JoeSpareBedroom]

Thanks, John. One more question, so I don't waste time looking for something
that can't be done: Using VBA, is there a way to trap for CTRL-F, stop it
from invoking the normal Find function, and redirect it to a different
routine, assuming I find one in the discussion you pointed me to? I don't
need details - just a yes or no is enough.

 

[Gord Dibben]

That functionality is built-in after Excel 97

Edit>Find>Options>Within. Enable "Workbook"


Gord Dibben MS Excel MVP

 

[Tom Hutchins]

That's easy... For example, to re-assign CTRL-F to a subroutine named MyFind
while that workbook is open, add this code in the ThisWorkbook module:

Private Sub Workbook_Activate()
'Assign macro shortcut = {Ctrl}f
Application.OnKey "^f", "MyFind"
End Sub

Private Sub Workbook_Deactivate()
'Clear macro shortcut = {Ctrl}f
Application.OnKey "^f", ""
End Sub

However, CTRL-F is disabled for any other workbooks that are open at the
same time as the workbook with this code. Relaunching Excel will restore the
normal CTRL-F functionality.

If you're new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Regarding your original question (I didn't read John's link yet), the
regular Find/Replace dialog provides at least two ways to search and/or
replace on multiple sheets. You can click the Options button, then change the
'Within' option to workbook instead of worksheet. The Find/Replace operation
will include all sheets in the workbook. Or, you can select multiple sheets
to search those sheets in one operation.

Hope this helps,

Hutch

 

[JoeSpareBedroom]

I'm using Excel 2000, and I see no Options thing available to me as you
described it. My only choices are:

Search by: Rows or Columns
Look In: Formulas, Values or Comments

Two check boxes: Match Case & Find Entire Cells Only

That's all I have.

 

[JoeSpareBedroom]

Tom, the CTRL-F subroutine is helpful, but the Options button doesn't exist
in Excel 2000.

 

[Dave Peterson]

You can use Jan Karel Pieterse's FlexFind:
http://www.oaltd.co.uk/MVP/

 

[Gord Dibben]

Have you tried grouping the sheets and do your Edit>Find?

Excel 97 would replace across grouped sheets but not find.

Don't know about 2000..........never used that version but I thought it had the
feature. Guess not.


Gord

 

[JoeSpareBedroom]

Grouping works. There's an interesting clue to work with.