Searching across all sheets

  • Thread starter JoeSpareBedroom
  • Start date
J

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?
 
J

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.
 
G

Gord Dibben

That functionality is built-in after Excel 97

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


Gord Dibben MS Excel MVP
 
T

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
 
J

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.
 
J

JoeSpareBedroom

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

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
 
Top