Anotherway using Find & FindNext which should be quicker..

Sub Macro()

Dim varFound As Variant, varSearch As Variant, ws As Worksheet
Dim strAddress As String, intCount As Integer, intSheet As Integer

varSearch = "word"

For intSheet = 2 To Sheets.Count
Set ws = Worksheets(intSheet)
Set varFound = ws.Cells.Find(varSearch, , xlValues, xlPart)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
intCount = intCount + 1
Sheets("Sheet1").Range("A" & intCount) = ws.Name
Sheets("Sheet1").Range("B" & intCount) = strAddress
Sheets("Sheet1").Range("C" & intCount) = varFound.Text
Set varFound = ws.Cells.FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < strAddress
End If
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

Dear all,

I want to use VBA to look for a word (for example "AAA") in one sheet.
but I want to find this word sheet by sheet and then copy the this word (if
find) to another sheet (for example,copy this word to sheet1)

I don't know how to loop the sheet(one by one) and how to look for this
work and then copy this word to another sheet.

Thank you