Thread: excel to read a row, if word 'x' in then put on other sheet ??

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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
4) Ctrl c or Edit>Copy
5) Insert>Worksheet
6) Ctrl v or Edit>Paste
7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
8) Press Esc
9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
>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" <rondebruin@kabelfoon.nl> wrote in message news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
> 3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
> 4) Ctrl c or Edit>Copy
> 5) Insert>Worksheet
> 6) Ctrl v or Edit>Paste
> 7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
> 8) Press Esc
> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
>>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" <rondebruin@kabelfoon.nl> wrote in message
news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
> 3) Press Alt ; to select all visible data or use F5>Special>Visible
cells only>OK
> 4) Ctrl c or Edit>Copy
> 5) Insert>Worksheet
> 6) Ctrl v or Edit>Paste
> 7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
> 8) Press Esc
> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> >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" <andy100@aog.com> wrote in message news:dijh5p$cft$1@news7.svr.pol.co.uk...
> 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" <rondebruin@kabelfoon.nl> wrote in message
> news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
>> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
>> 3) Press Alt ; to select all visible data or use F5>Special>Visible
> cells only>OK
>> 4) Ctrl c or Edit>Copy
>> 5) Insert>Worksheet
>> 6) Ctrl v or Edit>Paste
>> 7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
>> 8) Press Esc
>> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
>> >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" <rondebruin@kabelfoon.nl> wrote in message
news:#y2OaT1zFHA.2132@TK2MSFTNGP15.phx.gbl...
> 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" <andy100@aog.com> wrote in message
news:dijh5p$cft$1@news7.svr.pol.co.uk...
> > 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" <rondebruin@kabelfoon.nl> wrote in message
> > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> >> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
> >> 3) Press Alt ; to select all visible data or use F5>Special>Visible
> > cells only>OK
> >> 4) Ctrl c or Edit>Copy
> >> 5) Insert>Worksheet
> >> 6) Ctrl v or Edit>Paste
> >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my
example )
> >> 8) Press Esc
> >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> >> >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" <andy100@aog.com> wrote in message news:dijldt$95f$1@newsg2.svr.pol.co.uk...
> 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" <rondebruin@kabelfoon.nl> wrote in message
> news:#y2OaT1zFHA.2132@TK2MSFTNGP15.phx.gbl...
>> 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" <andy100@aog.com> wrote in message
> news:dijh5p$cft$1@news7.svr.pol.co.uk...
>> > 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" <rondebruin@kabelfoon.nl> wrote in message
>> > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
>> >> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
>> >> 3) Press Alt ; to select all visible data or use F5>Special>Visible
>> > cells only>OK
>> >> 4) Ctrl c or Edit>Copy
>> >> 5) Insert>Worksheet
>> >> 6) Ctrl v or Edit>Paste
>> >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my
> example )
>> >> 8) Press Esc
>> >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
>> > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
>> >> >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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Thanks Ron, it has a lot of confidential information in there, it relates to
work details addresses, mobile numbers, National Ins numbers. I haven't got
it here, it's on my work PC and i daren't send it from there because I would
get sacked for sending it over the net !!. They monitor e-mails and i'd get
into trouble.

Many Thanks for your speedy help, much appreciated.

Regards
Andrew



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:On55f01zFHA.596@TK2MSFTNGP12.phx.gbl...
> Hi Andy
>
> I like to see your workbook if possible
> Send it to me private
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Andy100" <andy100@aog.com> wrote in message
news:dijldt$95f$1@newsg2.svr.pol.co.uk...
> > 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" <rondebruin@kabelfoon.nl> wrote in message
> > news:#y2OaT1zFHA.2132@TK2MSFTNGP15.phx.gbl...
> >> 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" <andy100@aog.com> wrote in message
> > news:dijh5p$cft$1@news7.svr.pol.co.uk...
> >> > 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" <rondebruin@kabelfoon.nl> wrote in message
> >> > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> >> >> 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 Data>Filter>AutoFilter 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 F5>Special>Current
region>OK
> >> >> 3) Press Alt ; to select all visible data or use
F5>Special>Visible
> >> > cells only>OK
> >> >> 4) Ctrl c or Edit>Copy
> >> >> 5) Insert>Worksheet
> >> >> 6) Ctrl v or Edit>Paste
> >> >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my
> > example )
> >> >> 8) Press Esc
> >> >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> >> > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> >> >> >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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

OK


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


"Andy100" <andy100@aog.com> wrote in message news:dijmt7$r0l$1@news6.svr.pol.co.uk...
> Thanks Ron, it has a lot of confidential information in there, it relates to
> work details addresses, mobile numbers, National Ins numbers. I haven't got
> it here, it's on my work PC and i daren't send it from there because I would
> get sacked for sending it over the net !!. They monitor e-mails and i'd get
> into trouble.
>
> Many Thanks for your speedy help, much appreciated.
>
> Regards
> Andrew
>
>
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> news:On55f01zFHA.596@TK2MSFTNGP12.phx.gbl...
>> Hi Andy
>>
>> I like to see your workbook if possible
>> Send it to me private
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Andy100" <andy100@aog.com> wrote in message
> news:dijldt$95f$1@newsg2.svr.pol.co.uk...
>> > 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" <rondebruin@kabelfoon.nl> wrote in message
>> > news:#y2OaT1zFHA.2132@TK2MSFTNGP15.phx.gbl...
>> >> 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" <andy100@aog.com> wrote in message
>> > news:dijh5p$cft$1@news7.svr.pol.co.uk...
>> >> > 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" <rondebruin@kabelfoon.nl> wrote in message
>> >> > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
>> >> >> 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 Data>Filter>AutoFilter 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 F5>Special>Current
> region>OK
>> >> >> 3) Press Alt ; to select all visible data or use
> F5>Special>Visible
>> >> > cells only>OK
>> >> >> 4) Ctrl c or Edit>Copy
>> >> >> 5) Insert>Worksheet
>> >> >> 6) Ctrl v or Edit>Paste
>> >> >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my
>> > example )
>> >> >> 8) Press Esc
>> >> >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
>> >> > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
>> >> >> >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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Thanks Ron for your help. I think the problem is to do with the macros that
are in it. I tried it on an excel file i've got here which also has macros,
that's how i got that error message. I will try "easy filter" on the works
PC to see if it happens on the excel file in question, it may be ok on it.

Cheers
Andy



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:uUL8pF2zFHA.1264@tk2msftngp13.phx.gbl...
> OK
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Andy100" <andy100@aog.com> wrote in message
news:dijmt7$r0l$1@news6.svr.pol.co.uk...
> > Thanks Ron, it has a lot of confidential information in there, it
relates to
> > work details addresses, mobile numbers, National Ins numbers. I haven't
got
> > it here, it's on my work PC and i daren't send it from there because I
would
> > get sacked for sending it over the net !!. They monitor e-mails and i'd
get
> > into trouble.
> >
> > Many Thanks for your speedy help, much appreciated.
> >
> > Regards
> > Andrew
> >
> >
> >
> > "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> > news:On55f01zFHA.596@TK2MSFTNGP12.phx.gbl...
> >> Hi Andy
> >>
> >> I like to see your workbook if possible
> >> Send it to me private
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "Andy100" <andy100@aog.com> wrote in message
> > news:dijldt$95f$1@newsg2.svr.pol.co.uk...
> >> > 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" <rondebruin@kabelfoon.nl> wrote in message
> >> > news:#y2OaT1zFHA.2132@TK2MSFTNGP15.phx.gbl...
> >> >> 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" <andy100@aog.com> wrote in message
> >> > news:dijh5p$cft$1@news7.svr.pol.co.uk...
> >> >> > 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" <rondebruin@kabelfoon.nl> wrote in message
> >> >> > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> >> >> >> 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 Data>Filter>AutoFilter
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 F5>Special>Current
> > region>OK
> >> >> >> 3) Press Alt ; to select all visible data or use
> > F5>Special>Visible
> >> >> > cells only>OK
> >> >> >> 4) Ctrl c or Edit>Copy
> >> >> >> 5) Insert>Worksheet
> >> >> >> 6) Ctrl v or Edit>Paste
> >> >> >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my
> >> > example )
> >> >> >> 8) Press Esc
> >> >> >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> >> >> > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> >> >> >> >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" <andy100@aog.com> wrote in message news:dijh5p$cft$1@news7.svr.pol.co.uk...
> > 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" <rondebruin@kabelfoon.nl> wrote in message
> > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> >> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
> >> 3) Press Alt ; to select all visible data or use F5>Special>Visible
> > cells only>OK
> >> 4) Ctrl c or Edit>Copy
> >> 5) Insert>Worksheet
> >> 6) Ctrl v or Edit>Paste
> >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
> >> 8) Press Esc
> >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> >> >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" <andy100@aog.com> wrote in message news:dijh5p$cft$1@news7.svr.pol.co.uk...
> > > 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" <rondebruin@kabelfoon.nl> wrote in message
> > > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> > >> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
> > >> 3) Press Alt ; to select all visible data or use F5>Special>Visible
> > > cells only>OK
> > >> 4) Ctrl c or Edit>Copy
> > >> 5) Insert>Worksheet
> > >> 6) Ctrl v or Edit>Paste
> > >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
> > >> 8) Press Esc
> > >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> > > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> > >> >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
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
> >

Thanks Ewan,
I should have had payed more attention!
Stefi

"ewan7279" wrote:

> 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" <andy100@aog.com> wrote in message news:dijh5p$cft$1@news7.svr.pol.co.uk...
> > > > 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" <rondebruin@kabelfoon.nl> wrote in message
> > > > news:ODk4$n0zFHA.268@TK2MSFTNGP09.phx.gbl...
> > > >> 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 Data>Filter>AutoFilter 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 F5>Special>Current region>OK
> > > >> 3) Press Alt ; to select all visible data or use F5>Special>Visible
> > > > cells only>OK
> > > >> 4) Ctrl c or Edit>Copy
> > > >> 5) Insert>Worksheet
> > > >> 6) Ctrl v or Edit>Paste
> > > >> 7) Select the sheet with the filter ( Sheet("Netherlands") in my example )
> > > >> 8) Press Esc
> > > >> 9) Press Alt d f f or Data>Filter>AutoFilter 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" <andy100@aog.com> wrote in message
> > > > news:dijbmf$kb8$1@newsg3.svr.pol.co.uk...
> > > >> >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
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >