Excel FIND ALL-How do you extract the findings to a new worksheet

B

BelAir

Using the 'FIND ALL' option in excel brings a box of seletions based on my
search criteria. I would like to take those findings and make a new Excel
worksheet with them.
 
R

Ron Coderre

It makes a difference if the matched cells will all be in the same row or
column versus scattered all over the worksheet.

If they are scattered all over the worksheet....we need more information to
be able to help....particularly, what the rules are.

If they will all be in the same COLUMN or ROW and you only want the matched
cells copy/pasted:
Edit>Find...[Find All]
[Ctrl]+A (to select all matches)
Press [Escape] 1 time to clear the find screen
Edit>Copy
File>New
Press [Enter] to past the items.

If the matched cells will all be in the same COLUMN and you want the
associated cells from the SAME ROW to be copied, you'd do better to use a
Data Filter to select the cells before copy/pasting.

If the matched cells will all be in the same ROW and you want the associated
cells from the same COLUMN to be copied, you could:
Edit>Find for the cells that DO NOT meet your criteria...then..
Format>Column>Hide
Edit>GoTo...[Special Cells]...Current Region......[Enter]
Edit>GoTo...[Special Cells]...Visible Cells only....[Enter]
Then copy/paste where you want.

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
G

Gord Dibben

Belair

After you have done the "Find All" step you are presented with a dialog box
with found items.

Click on top item then hold SHIFT key and scroll down to last item using
downarrow. Click on that to select all.

You will see these cells selected on your sheet.

Close the dialog and Edit>Copy.

Insert>Worksheet. Select A1 and Paste.


Gord Dibben Excel MVP
 
T

tghcogo

It would be a very useful to me as well, but unfortunately neither of
the above solutions seem to work, I'm using Excel 2003 on windows XP.
 
B

BelAir

Thank you, is there a way to grab the entire row and have that copied?
Assuming that your original worksheet has ove r700 entries and your 'found
items' equal approx 150.
 
R

Ron Coderre

If you used Data>Filter>Autofilter to find the matching data....

Select from the column heading down through the last matched item.

Hold down the [Shift] key and press the [Space Bar]
(the entire rows will be selected)

[Ctrl]+C (to copy...the moving border means only the visible rows are
selected)

Switch to another sheet and press [Enter] (to paste)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
G

Gord Dibben

I tested my method on one column only and it works.

BUT..........Does not work if data is in more than one column.

Get the "cannot be used on multiple selections"


Gord
 
G

Gord Dibben

Belair

I tested this method on one column only and it worked.

When data is found in more than one column it errors out with the "cannot do
this on multiple selections".

Poor testing leads to poor advice<g>

See Ron's suggestion on using Filter.


Gord
 
Top