vba searching for strings in multiple worksheets

[justinhale@gmail.com]

Hi,

I am trying to search multiple worksheets for the word "Fail" and then
copy all of the "fail" data that has a failure into another worksheet.
I am able to copy one sheet to another, but cannot figure out how to
search multiple sheets.

The number of worksheets are dynamic. The user can add sheets to the
workbook.

Here is my working code for one sheet:

With Sheet2
For i = 1 To .UsedRange.Rows.Count

If .Cells(i, 1) = "Fail" Then

'Offset to select the testcase number
Sheet1.Cells(j, 3) = Sheet2.Cells(i, 2)

'Offset to select the Serial Number
Sheet1.Cells(j, 2) = Sheet2.Cells(i, 4)

j = j + 1

Else
Sheet1.range("B15:c500") = ""

End If
Next
End With

Thank you

[jetted]

Hi Justin

See attached document
every "Fail" should be stored in worksheet "Fail_Workbook"

Thanks
Denis