Jun 20 2022 03:42 AM
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.
Jun 20 2022 05:15 AM
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.
I know I don't know anything (Socrates)