Option Explicit
Sub SearchAndOutput()
Dim ws As Worksheet
Dim i As Long
' Requesting the desired value from the user
Dim searchValue As String
searchValue = InputBox("Enter a value to search for:", "Search")
If searchValue = "" Then Exit Sub
' Create an array to store the results
Dim tempList As Collection
Set tempList = New Collection
' We go through all the pages of the book
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then
' Find the last row in column A
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow >= 1 Then
' Loading data from columns A:C into an array
Dim dataArr As Variant
dataArr = ws.Range("A1:C" & lastRow).Value
' We go through the array
For i = 1 To UBound(dataArr, 1)
If CStr(dataArr(i, 1)) = searchValue Then
' Add the entire string to the collection
tempList.Add Array(dataArr(i, 1), dataArr(i, 2), dataArr(i, 3))
End If
Next i
End If
End If
Next ws
' If there are any found values, write them to Sheet2 starting with B3
If tempList.Count > 0 Then
Dim outputArr() As Variant
ReDim outputArr(1 To tempList.Count, 1 To 3)
For i = 1 To tempList.Count
outputArr(i, 1) = tempList(i)(0)
outputArr(i, 2) = tempList(i)(1)
outputArr(i, 3) = tempList(i)(2)
Next i
' Clearing previous results
ThisWorkbook.Worksheets("Sheet2").Range("B3:D" & Rows.Count).ClearContents
' Writing an array to a sheet
ThisWorkbook.Worksheets("Sheet2").Range("B3").Resize(UBound(outputArr, 1), 3).Value = outputArr
Else
MsgBox "The horse name you were looking for was not found!"
End If
End Sub
