User friendly search

Copper Contributor

Hi, 

I have a excel file with around 40 sheets in it and they have all diffrent structures, i would like to make a search box or button or whatever to search across the whole workbook, and then make the results into a table on a new sheet. I'm looking for a solution for days but had no luck, if someone can help me i'd really appreciate it. 

1 Reply

@MaksimVaranytsia 

I would recommend searching on all worksheets to do it best with VBA macros

...here is a macro example.

 

Sub Search()
Dim booFound As Boolean
Dim strInput As String
Dim rngField As Range
Dim wksSheet As Worksheet
strInput = Application.InputBox("Please enter a search term", "Search", "Search term")
If strInput <> "" Then
For Each wksSheet In ActiveWorkbook.Worksheets
For Each rngField In wksSheet.UsedRange
If InStr(UCase(CStr(rngField.Value)), UCase(strInput)) > 0 Then
booFound = True
wksSheet.Activate
rngField.Select
If MsgBox("Find Next?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
End If
Next rngField
Next wksSheet
If booFound = False Then MsgBox "'" & strInput & "' not found!", vbInformation
End If
End Sub

 

 

...or

Activate the relevant workbook.

Start the search dialog by pressing the key combination Shift + F5 or Ctrl + F.

Click the "Options" button here.

Additional search parameters are added to the dialog box.

From the Search list box, select Workbook.

Then click the Find All button.

 

 

I would be happy to know if I could help.

 

NikolinoDE

I know I don't know anything (Socrates)