Excel - Search all worksheets

[Anna22]

Hi
Can any of you help me please. I have a workbook with severall worksheets in. I want to do a search on every worksheet within that workbook for a particular word (i.e. a street name) when i select all worksheets and then go to find it only seems to search the worksheet that i am on . Am i doing something wrong?

Cheers

Anna

 

[SkipVought]

Anna,

You'll have to automate your process using a macro.

Record performing the search on one sheet. Then post back with your code and we'll help you customize it to search your workbook.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Anna22]

Thanks for the reply. If the search word changes will i have to keep amending the macro?

 

[SkipVought]

No, you MODIFY the macro so a user can feed different data to it.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

If you have XP - there is an option on the FIND dialog box to search the whole workbook

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Anna22]

Hi

"Sub Find()
'
' Find Macro
' Macro recorded 20/07/2004 by admin
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Cells.Find(What:="crown", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End Sub"

Its only a very basic find but it has to search several worksheets

Cheers

 

[Anna22]

Hi xlbo

We are running 97 at the moment, we are looking to upgrade soon

Thanks anyway

Anna

 

[SkipVought]

Sub Find()
   Dim f() As Range
   For Each ws In Worksheets
      With ws
         .Activate
         ThisAdd = ""
         Do
            i = i + 1
            PrevAdd = ThisAdd
            ReDim Preserve f(i)
            Set f(i) = .Cells.Find(What:="crown", after:=ActiveCell)
            If Not f(i) Is Nothing Then Exit Do
            f(i).Select
            ThisAdd = f(i).Address
         Loop Until ThisAdd < PrevAdd
      End With
   Next
   For Each r In f
      r.Parent.Activate
      r.Select
      MsgBox ""
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244