Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find text in a protected worksheet | Excel Discussion (Misc queries) | |||
Find text in a protected worksheet | Excel Discussion (Misc queries) | |||
Find text - worksheet function or macro? | Excel Discussion (Misc queries) | |||
FIND A TEXT IN A WORKSHEET NOT IN A COLUMN OR ROW BY FORMULA | Excel Discussion (Misc queries) | |||
Excel: Find/Replace for Data Validation text or Worksheet Objects | Excel Discussion (Misc queries) |