Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Search multiple sheets, then paste results in new sheet
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Search multiple sheets, then paste results in new sheet
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple search results | Excel Worksheet Functions | |||
search, copy and paste through multiple sheets | Excel Discussion (Misc queries) | |||
multiple results from search / how to? | Excel Discussion (Misc queries) | |||
Copy paste WkBk/sheet 1 to multiple wkbks/sheets | Excel Programming | |||
multiple results search | Excel Programming |