Hello @AliceBryant-n4m, You are most welcome. There is a way to clear the search results. Here’s how you can do it: 1. Define the specific worksheet and the range where the search results are displayed. 2. Clear the contents and formatting of that range. Copy-paste the following VBA code: Sub ClearSearchResults() Dim searchSheet As Worksheet Dim searchBox As Range Dim searchRange As Range ' Define the search sheet and search box location (adjust as needed) Set searchSheet = Sheets("VBA") Set searchBox = searchSheet.Range("B5") ' Define the range where the search results are displayed (adjust as needed) Set searchRange = searchSheet.Range("B9:F1000") ' Clear the contents and formatting of the search results searchRange.ClearContents searchRange.ClearFormats ' Clear the search term in the search box searchBox.Value = "" End Sub To add the Button: Go to the "Developer" tab. Click "Insert" and choose a button. Draw the button on the sheet. Assign the ClearSearchResults macro to the button. Here's the updated Excel File: www.exceldemy.com/wp-content/uploads/2024/07/Clear-Search-Box-for-Multiple-Sheets-1.xlsm Regards ExcelDemy

@@exceldemy2006 Amazing! Thanks so much :)

@AliceBryant-n4m You are most welcome. Thanks for your appreciation, it means a lot to us. Keep learning Excel with ExcelDemy.

Dear, Thanks for your question! You want a sub-procedure that will not populate any result of the empty search box. Don't worry! We have modified the existing code based on your requirements. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Output-of-using-a-sub-procedure-that-will-not-populate-any-result-of-the-empty-search-box.gif You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Coldsprouts-SOLVED.xlsm Excel VBA Sub-procedure: Sub SearchMultipleSheets() Main_Sheet = "VBA" Search_Cell = "B5" SearchType_Cell = "C5" Paste_Cell = "B9" Searched_Sheets = Array("Dataset 1", "Dataset 2") Searched_Ranges = Array("B5:F23", "B5:F23") Copy_Format = True Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column)) Used_Range.ClearContents Used_Range.ClearFormats Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value Count = -1 If Sheets(Main_Sheet).Range(Search_Cell).Value = "" Then Exit Sub End If If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then Case_Sensitive = True ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then Case_Sensitive = False Else MsgBox ("Choose a Search Type.") Exit Sub End If For S = LBound(Searched_Sheets) To UBound(Searched_Sheets) Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S)) For i = 1 To Rng.Rows.Count For j = 1 To Rng.Columns.Count Value2 = Rng.Cells(i, j).Value If PartialMatch(Value1, Value2, Case_Sensitive) = True Then Count = Count + 1 Rng.Rows(i).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column) If Copy_Format = True Then Paste_Range.PasteSpecial Paste:=xlPasteAll Else Paste_Range.PasteSpecial Paste:=xlPasteValues End If End If Next j Next i Next S Application.CutCopyMode = False End Sub Function PartialMatch(Value1, Value2, Case_Sensitive) Matched = False For i = 1 To Len(Value2) If Case_Sensitive = True Then If Mid(Value2, i, Len(Value1)) = Value1 Then Matched = True Exit For End If Else If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then Matched = True Exit For End If End If Next i PartialMatch = Matched End Function

Hello @user-gr6fl1mk2p, Sorry for the inconvenience. Please check out our updated article you will get the same code there : www.exceldemy.com/create-a-search-box-in-excel-for-multiple-sheets/#2_Create_a_Search_Box_in_Excel_for_Multiple_Sheets_with_VBA Also, you can download our updated Excel File. Thanks for watching our videos. Please stay connected with us. Regards ExcelDemy

Hi and thank you for this video. I am not computer savvy so I am racking my brain with this portion of the code: For i = 1 To Len(Value2) I copied the code from your embedded link but it keeps stopping at that line. Do you have any advice? Thanks

@@michaelwinsor511 Dear, Thanks for your kind words. The provided code is working perfectly on our end. You have copied the code from the link, but it stops in a particular line. Providing an ultimate solution without glancing at your dataset and being remote is difficult. So, we recommend that you share your problem within the ExcelDemy Forum. Here, you can also share your workbook and other necessary documents. ExcelDemy Forum: exceldemy.com/forum/

@@exceldemy2006 I am having this same issue as @michaelwinsor511 with the Error 13 for the line "For i = 1 To Len(Value2)" I've dug through your post on the website and a few other sites for "Error 13" but still can't see how the error would populate through the code. I've created a forum with the link provided above, so hopefully we can get the solution posted for others out there who may have this problem later.

@@brandychapman9375 Dear, Thanks for sharing your problem! Error 13 Type Mismatch in VBA typically occurs when you try to operate on incompatible data types. The Value2 variable contains cell values when looping through and comparing with Value1. Some of your values seem to contain errors, so it is impossible to use the Len function with this value. So, to avoid this type of situation, you can use IsError to check whether the cells contain any errors. If not, operate; otherwise, do nothing.

Hello @branimirperkovic, If you have over 100 worksheets either you need to mention them in the Searched_Sheets and Searched_Ranges or you can use this updated code. Here the code will loop through all the available sheets in the workbook. Sub SearchAllSheets() Dim ws As Worksheet Dim SearchTerm As String Dim Cell As Range Dim FirstAddress As String ' The search term is taken from a specific cell, e.g., B1 on the "VBA" sheet SearchTerm = Sheets("VBA").Range("B1").Value If SearchTerm = "" Then MsgBox "Please enter a search term in cell B1 of the VBA sheet." Exit Sub End If For Each ws In ThisWorkbook.Worksheets ws.Cells.Interior.ColorIndex = xlNone With ws.UsedRange Set Cell = .Find(SearchTerm, LookIn:=xlValues, LookAt:=xlPart) If Not Cell Is Nothing Then FirstAddress = Cell.Address Do Cell.Interior.Color = vbYellow Set Cell = .FindNext(Cell) Loop While Not Cell Is Nothing And Cell.Address FirstAddress End If End With Next ws MsgBox "Search Complete!" End Sub But if you wish to use the code used in the video you need to do the following adjustments in the searched sheets and ranges. Searched_Sheets = Array("Dataset 1", "Dataset 2", "Dataset 3", "Dataset 4", "Dataset 5", _ "Dataset 6", "Dataset 7", "Dataset 8", "Dataset 9", "Dataset 10", _ "Dataset 11", "Dataset 12", "Dataset 13", "Dataset 14", "Dataset 15", _ "Dataset 16", "Dataset 17", "Dataset 18", "Dataset 19", "Dataset 20", _ "Dataset 21", "Dataset 22", "Dataset 23", "Dataset 24", "Dataset 25", _ "Dataset 26", "Dataset 27", "Dataset 28", "Dataset 29", "Dataset 30", _ "Dataset 31", "Dataset 32", "Dataset 33", "Dataset 34", "Dataset 35", _ "Dataset 36", "Dataset 37", "Dataset 38", "Dataset 39", "Dataset 40", _ "Dataset 41", "Dataset 42", "Dataset 43", "Dataset 44", "Dataset 45", _ "Dataset 46", "Dataset 47", "Dataset 48", "Dataset 49", "Dataset 50", _ "Dataset 51", "Dataset 52", "Dataset 53", "Dataset 54", "Dataset 55", _ "Dataset 56", "Dataset 57", "Dataset 58", "Dataset 59", "Dataset 60", _ "Dataset 61", "Dataset 62", "Dataset 63", "Dataset 64", "Dataset 65", _ "Dataset 66", "Dataset 67", "Dataset 68", "Dataset 69", "Dataset 70", _ "Dataset 71", "Dataset 72", "Dataset 73", "Dataset 74", "Dataset 75", _ "Dataset 76", "Dataset 77", "Dataset 78", "Dataset 79", "Dataset 80", _ "Dataset 81", "Dataset 82", "Dataset 83", "Dataset 84", "Dataset 85", _ "Dataset 86", "Dataset 87", "Dataset 88", "Dataset 89", "Dataset 90", _ "Dataset 91", "Dataset 92", "Dataset 93", "Dataset 94", "Dataset 95", _ "Dataset 96", "Dataset 97", "Dataset 98", "Dataset 99", "Dataset 100") Searched_Ranges = Array("B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23", _ "B5:F23", "B5:F23", "B5:F23", "B5:F23", "B5:F23") Regards ExcelDemy

@@exceldemy2006 Thank you very much for such a detailed answer. I will certainly try the first variant, because with the second one there is a very high chance of making a mistake in listing so many names or skipping one.

You are most welcome @branimirperkovic.