Finding data across multiple worksheets withing a workbook

[Mike_Dean]

Hi,

Therse is probably an easy answer (I'm hoping).
My spreadsheet contains some 30 seperately named sheets, eg Cat, Dog etc, etc.

I have an index sheet hyperlinked to each sheet, on that index sheet, I want to put a 'Find' function, that will look across each sheet and go to the specific sheet containing the text (or par thereof). Not to unlike the standard find function, but having many sheets and 15 users, means ensuring they all know how to right click, select all sheets, then use the find function.

I'd rather have a search box on the main sheet, whereby text could be entered, and then hit a control button, eg search and the rest is history as they say..

Using Excel 2000....


Any helpers..????

 

[Peter van Vliet]

Hello Mike,

The text you want to find must be in the cursor cell - this might save unnecessary typing

You must change D1 and INDEX_SHEET to suit your file
The macro will dump all occurences starting D2 in INDEX_SHEET

Sub Macro1()

FIND_THIS = ActiveCell
Range("D1").Select
Range("D1") = "TEXT FOUND IN ..."

For Each Sht In ActiveWorkbook.Sheets
On Error Resume Next
ACTIVE_NAME = Sht.Name

If ACTIVE_NAME <> "INDEX_SHEET" Then
Sheets(ACTIVE_NAME).Select
Cells.Select
Selection.Find(What:=FIND_THIS).Activate
ActiveCell.Select

If ActiveCell.Address <> "$A$1" Then
Sheets("INDEX_SHEET").Select
ActiveCell.Offset(1, 0).Select
ActiveCell = ACTIVE_NAME
End If
End If
Next

Sheets("INDEX_SHEET").Select

End Sub

Regards,
Peter

 

[Mike_Dean]

This kind a worked.. but wasn;t fussed on the way the screen "Wobbles" when searching... any other ideas anyone..?

 

[Zack Barresse]

Hello,

The "wobbling" screen is from so much selecting. This doesn't really need to be there. You could shorten this up to something like this ...

Sub Macro1()
    Dim FIND_THIS As Variant, Sht As Worksheet, foundRng As Range, cel As Range
    FIND_THIS = ActiveCell
    Set cel = Range("D1")
    cel.Value = "TEXT FOUND IN ..."
    For Each Sht In ActiveWorkbook.Sheets
'        On Error Resume Next
        If Sht.Name <> "INDEX_SHEET" Then
            Set foundRng = Nothing
            Set foundRng = Sht.Cells.Find(FIND_THIS)
            If Not foundRng Is Nothing Then
                If foundRng.Address <> "$A$1" Then
                    cel.Offset(1) = Sht.Name
                    Set cel = cel.Offset(1)
                End If
            End If
        End If
    Next
    Sheets("INDEX_SHEET").Select
End Sub

Take out the last line (Sheets("INDEX_SHEET").Select) if you don't want to go to that sheet upon completion of the code.


HTH

 

[Mike_Dean]

Aha.. Thats getting closer.. Now to add more dilema's. The same part number may appear on many sheets, eg, searching for PBIH could lead you to 3 sheets, eg PBIH-XXXD on one sheet and PBIH-XX12 on another.

Is it possible to have, like the Find Option works, a poop up box that goes to the first record, or sheet, then on 'Enter' goes to the next record, be it that sheet or another one..????

Thanks....

 

[Zack Barresse]

Hi,

I don't follow. What part of the search criteria is varialbe here? Although you only want to search for specific criteria on specific sheets?

 

[Mike_Dean]

Firstly, thanks for the support,


My main sheet is an index sheet which is named Supplier Index. I have another 11 sheets that contain many variants of part numbers, eg

Sheet name 'ETA' would have part number PBIH-D-J, or PBIH-C-F, etc another supplier sheet 'Cosel' might have the a similar part number, eg PBIH-4-J etc.

The version of Office I'm running, (2000) limits the 'Find' function to the sheet you are on. (Whihc mean i have to educate the guys to use the right click, select all sheets' option) I know other versions, (XP.?) provide the option to 'Look in this sheet, or all sheets..( or similar wording), I was hoping for a Macro to be able to mimic this function.? Eg, type the part number in a particular Cell, eg H4, run the Macro, which would lead you to the first sheet with the occurance, and then 'on enter' go to the next occurance, until there were no more..?

Is this too difficult..?

 

[Zack Barresse]

No, it's not difficult, but you wouldn't be able to edit the cell while the code was running, like you can with the native function.