Find text and print its corresponding worksheet name and row
 

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

Find text and print its corresponding worksheet name and row
 

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

"Sundar" wrote:

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