Auto find a specific row and copy it to another worksheet

[hme]

Hi

let me explain more clear

how can excel find automatically a given text in all worksheets (i
there is any) of a given workbook and copy the row where this text i
to another worksheet.

should be easy but I can't

sohrab hashem

 

[Tom Ogilvy]

Assume we will copy to Sheet2 (so don't search that sheet)


Sub CopyInfo()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
if lcase(sh.name) <> "sheet2" then
set rng = sh.Cells.Find("textString")
if not rng is nothing then
rng.EntireRow.copy Destination:= _
worksheets("Sheet2").Cells(rows.count,1).End(xlup)(2)
exit sub
end if
end if
Next
msgbox "TextString not found"
End Sub

Find has many arguments which correspond to choices in the Edit=>Find
dialog. Some of these arguments are persistent and could affect the outcome
of the find command. It is usually safer to specify them all explicitely.
You can turn on the macro recorder and do an Edit=>Find to get code that
shows how to set them all and then look in the Excel VBA help file for an
explanation of each along with alternate values for the arguments.