Search multiple sheets, then paste results in new sheet

[Paul M]

Hello,

I searched the web and newsgroups but couldn't find a way
to do this. I would like to search multiple sheets in a
particular column and paste the complete row containing
the searched word into a new sheet.

For example, suppose I have 2 sheets (sheet1 and sheet2),
each containing 3 colums (item_number, description, brand).
I would like to search for all items that are Acme brand,
and have the results (complete row) pasted into a new
sheet (sheet3).

Anyone know how this can be done? I would truly appreciate
any help with this.

Best regards,
Paul

[Tom Ogilvy]

Dim sAdd as String, v as Variant
Dim sh as Worksheet, rng as Range
dim rng1 as Range, i as Long
v = Array("Sheet1","Sheet2")
for i = lbound(v) to ubound(v)
set sh = Worksheets(v(i))
set rng = sh.Columns(3)
set rng1 = rng.find("Acme")
if not rng1 is nothing then
sAdd = rng1.Address
do
rng1.Entirerow.copy Destination:= _
Worksheets("Sheet3").Cells(rows.count,1).End(xlup)(2)
set rng1 = rng.FindNext(rng1)
Loop while rng1.Address <> sAdd
end if
Next

Find has several arguments that may be critical to getting the type of match
you want. Turn on the macro recorder and do a find on your sheet. Then
turn off the macro recorder and look at the recorded code. This will give
you an idea of the additional arguments which you might want to use. Be
aware that several of the arguments are persistent in terms of their
settings, so it is better to set them explicitly in your code each time you
use them. For brevity, I have not done that.

--
Regards,
Tom Ogilvy

"Paul M" <plmr-...@flashmail-nospam.com> wrote in message
news:cvi2s1t3crkdahvqb...@4ax.com...