Option Explicit
Private results() As String
Private index As Long
Sub BigFind()

Dim startaddress As String
Dim ws As Worksheet
Dim cell As Range
Dim what As String


what = "dog"

For Each ws In Worksheets

Set cell = ws.Cells.Find(what)
If Not cell Is Nothing Then
startaddress = cell.Address
Do
index = index + 1
ReDim Preserve results(1 To 2, 1 To index)
results(1, index) = ws.Name
results(2, index) = cell.Address
Set cell = ws.Cells.FindNext(cell)
Loop While cell.Address < startaddress
End If
Next

Set ws = Worksheets.Add
With ws
.Range(.Range("A1"), .Cells(UBound(results, 2), UBound(results, 1)))
= _
WorksheetFunction.Transpose(results)
End With

End Sub


"Pawan" wrote:

Hi,

Can we arite a mcro to find text/number in all wprksheets of a file at one
time.

Thank You,
Pawan