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.
I would recommend searching on all worksheets to do it best with VBA macros
...here is a macro example.
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
If MsgBox("Find Next?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
If booFound = False Then MsgBox "'" & strInput & "' not found!", vbInformation
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.