Find text and print its corresponding worksheet name and row

[Guest]

Hi all,
I have around 30 worksheets. Each work sheet has some values in it. Around
10 to 1000 rows of data in each sheet. Now, I have to find a text called,
say, "apple" in all the sheets and print the sheet name, and rows values in
the 31st sheet.

I hope the above explanation is understandable.

Thanks in advance.
Regards,

Sundar

 

[Guest]

Not many clues as the where we might find the apples so this looks in column A

Sub selectiveprint()
Dim wSheet As Worksheet
Dim MyRange As Range
For Each wSheet In Worksheets
wSheet.Select
If wSheet.Name = "31" Then Exit Sub
Set MyRange = Range("A1:A200")
For Each c In MyRange
c.Select
If c.Value = "apple" Then
Selection.EntireRow.Copy
LastRow = Sheets("31").Range("A65536").End(xlUp).Row
Worksheets("31").Range("A" & LastRow + 1) =
ActiveSheet.Name
Worksheets("31").Range("A" & LastRow + 2).PasteSpecial
End If
Next
Next
End Sub


Mike