search box in blank worksheet, display results from other sheets in workbook

[citogrid]

hi,
Obvious newbie question: I'm working with a fairly large list of information, not very complex, but spread over 50-some worksheets in one workbook. I'd like to create a blank worksheet with a simple searchbox and a simple button to click.

The results of the search should be displayed just below the searchbox.


Any help would be greatly appreciated.

 

[James006]

Hi,

You could test following macro to Search All Sheets :

[COLOR=#252C2F][FONT=Courier]Sub SearchAllSheets()
' Author : Dave Hawley
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]Dim ws As Worksheet
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]Dim rFound As Range
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]Dim strName As String
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]    On Error Resume Next
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]    strName = InputBox("What Name?")
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]    If strName = "" Then Exit Sub
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]    For Each ws In Worksheets
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]        With ws.UsedRange
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]           Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]               If Not rFound Is Nothing Then
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]                  Application.Goto rFound, True
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]                  Exit Sub
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]               End If
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]        End With
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]     Next ws
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]     On Error GoTo 0
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]     MsgBox "Value not found"
[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]End Sub
[/FONT][/COLOR]

Hope this will help

 

[citogrid]

Hi James,
Thanks for your help, points me in the right direction. Tested on mac excel, older version, older computer,... macros seem to cause some trouble, at times crashing (not caused by your code but generally not liking macros in excel I guess).

Will test on pc this week

thanks again,

Ben

 

[James006]

You are welcome ...

Hope solution will be operational on your PC ...