excel 2000 - using find in all sheets

  badgermansix 13:08 23 Mar 2008

Is there any way that I can configure "Find" in Excel 2000, so that it will look in each sheet automatically, i.e. if I use find in sheet 1, it will scan sheet 1, and then look in sheets 2,3 etc.
At the moment I have to use "Find" in each indiviual sheet to look for something.


  Fermat's Theorem 13:58 23 Mar 2008

in Excel 2007 there is an option to find "within sheet" or "within workbook".

Hope this helps :-)

  VoG II 14:16 23 Mar 2008

One way click here

Or you can use a macro

Sub FindAllSheets()
Dim Found As Range, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter value to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = ws.Cells.Find(what:=LookFor)
If Not Found Is Nothing Then Exit For
Next ws
If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Application.Goto reference:=Found, scroll:=True
End If
End Sub

To use this press ALT + F11 to open the Visual Basic Editor, Insert > Module then copy and paste the above code. Close the VBE using the X. Tools > Macro > Macros, highlight FindAllSheets and press the Run button.

  badgermansix 17:54 24 Mar 2008

Thank you both, I think VoG™, solution is the obvious for me, use the shift key to select the pages required, as I only have a few it is ideal.
I did have a look for a solution, and have quite a large informative book on Excel, but it just did not mention anything about seqrching all pages.

Thanks again.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?