Search all sheets in workbook

[mike0123m]

i have the following code which searches through all the worksheets in a workbook for certain words that I enter in a search box. It works well but it will only post results for 3 instances only as if there is a limit set in the code. Why will it not list more than 3 results?

Private Function SheetExists(SheetName As String) As Boolean
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
Sub FindAllSheets()
Dim Found As Range, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter search criteria:")

If LookFor = "" Then Exit Sub

If SheetExists("Search Results") Then
Sheets("Search Results").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Search Results"
End If

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Search Results" Then
Set Found = ws.Cells.Find(What:=LookFor)
If Found Is Nothing Then
Range("D5").Select
Else
Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
End If
Next ws
End Sub

 

[John_w]

Because it is searching only once in each sheet. You need to call the FindNext method to repeat the search. The VBA Help for Find and FindNext has an example Find/FindNext loop.