vba searching for strings in multiple worksheets

[justinhale]

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

 

[Dave Peterson]

Maybe...

dim wks as worksheet
for each wks in activeworkbook.worksheets
if lcase(wks.codename) = lcase("sheet1") then
'skip this sheet
else
With wks
For i = 1 To .UsedRange.Rows.Count
If .Cells(i, 1) = "Fail" Then
'Offset to select the testcase number
Sheet1.Cells(j, 3) = .Cells(i, 2)

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

j = j + 1

Else
'still want to do this???
Sheet1.range("B15:c500") = ""
End If
Next i
End With
end if
next wks

(untested, uncompiled. Watch for typos!)

 

[jetted]

Hi Justin

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

Thanks
Deni

+-------------------------------------------------------------------
|Filename: Book2.zip
|Download: http://www.excelforum.com/attachment.php?postid=5108
+-------------------------------------------------------------------