I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A and
if it sees the surname "Smith" i want it to copy the whole entry (whole row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is, i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message ...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A and
if it sees the surname "Smith" i want it to copy the whole entry (whole row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is, i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Hi Andy100

Forgot to add :
In the example i filter the Country field for the country Netherlands as you can see.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message ...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A and
if it sees the surname "Smith" i want it to copy the whole entry (whole row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is, i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering !

Thanks anyway
Andrew



"Ron de Bruin" wrote in message
...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to
activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible
cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A
and
if it sees the surname "Smith" i want it to copy the whole entry (whole
row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is,
i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows
it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Hi Andy

Try the EasyFilter Add-in first to copy the records to a new sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message ...
That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering !

Thanks anyway
Andrew



"Ron de Bruin" wrote in message
...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to
activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible
cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A
and
if it sees the surname "Smith" i want it to copy the whole entry (whole
row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is,
i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows
it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Using easyfilter i get error message "run-time error 1004 - sort method of
range class failed"

I have macros in the spreadsheet, does that matter ?

Cheers
Andy


"Ron de Bruin" wrote in message
...
Hi Andy

Try the EasyFilter Add-in first to copy the records to a new sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering !

Thanks anyway
Andrew



"Ron de Bruin" wrote in message
...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to
activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible
cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my
example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column
A
and
if it sees the surname "Smith" i want it to copy the whole entry
(whole
row)
into sheet 2.

there may be 20 or so queries running at any given time though. That
is,
i
might want it to copy all occurances of surnames "Smith" and "Jones"
and
"Brown" etc etc etc into sheet 2. not particularly bothered which
rows
it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Hi Andy

I like to see your workbook if possible
Send it to me private



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message ...
Using easyfilter i get error message "run-time error 1004 - sort method of
range class failed"

I have macros in the spreadsheet, does that matter ?

Cheers
Andy


"Ron de Bruin" wrote in message
...
Hi Andy

Try the EasyFilter Add-in first to copy the records to a new sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering !

Thanks anyway
Andrew



"Ron de Bruin" wrote in message
...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to
activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible
cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my
example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column
A
and
if it sees the surname "Smith" i want it to copy the whole entry
(whole
row)
into sheet 2.

there may be 20 or so queries running at any given time though. That
is,
i
might want it to copy all occurances of surnames "Smith" and "Jones"
and
"Brown" etc etc etc into sheet 2. not particularly bothered which
rows
it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Hi Ron,

Where can I find EasyFilter Add-in?

Regards,
Stefi


"Ron de Bruin" wrote:

Hi Andy

Try the EasyFilter Add-in first to copy the records to a new sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message ...
That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering !

Thanks anyway
Andrew



"Ron de Bruin" wrote in message
...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to
activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible
cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A
and
if it sees the surname "Smith" i want it to copy the whole entry (whole
row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is,
i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows
it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew

Stefi,

Follow the link in Ron's 2nd post

Ewan

"Stefi" wrote:

Hi Ron,

Where can I find EasyFilter Add-in?

Regards,
Stefi


"Ron de Bruin" wrote:

Hi Andy

Try the EasyFilter Add-in first to copy the records to a new sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message ...
That's fine for one filter, but i need to do a lot of filtering and that
might be a bit cumbersome with filtering !

Thanks anyway
Andrew



"Ron de Bruin" wrote in message
...
Hi Andy100

You can Autofilter and copy
See Debra's site also
http://www.contextures.com/xlautofilter01.html

Activate AutoFilter:
Select a cell in your data table and use DataFilterAutoFilter to
activate AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Country field and choose Netherlands.

Copy the filter result
1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK
3) Press Alt ; to select all visible data or use F5SpecialVisible
cells onlyOK
4) Ctrl c or EditCopy
5) InsertWorksheet
6) Ctrl v or EditPaste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


Another way is to use EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Andy100" wrote in message
...
I have a large database which i'm continually updating. It has:

Col A Col B Col C Col D

Surnames First names Time In Time Out



what i'm wanting to do (in sheet 2) is for excel to look down column A
and
if it sees the surname "Smith" i want it to copy the whole entry (whole
row)
into sheet 2.

there may be 20 or so queries running at any given time though. That is,
i
might want it to copy all occurances of surnames "Smith" and "Jones" and
"Brown" etc etc etc into sheet 2. not particularly bothered which rows
it
copies them into as i can do a "sort" on them later.

Hope this makes sense !!!!

Regards
Andrew