+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Andy100
    Guest

    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



  2. #2
    Ron de Bruin
    Guest

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

    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" <[email protected]> wrote in message news:[email protected]...
    >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
    >
    >




  3. #3
    Ron de Bruin
    Guest

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

    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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message news:[email protected]...
    >>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
    >>
    >>

    >
    >




  4. #4
    Andy100
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > >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
    > >
    > >

    >
    >




  5. #5
    Ron de Bruin
    Guest

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

    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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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" <[email protected]> wrote in message

    > news:[email protected]...
    >> >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
    >> >
    >> >

    >>
    >>

    >
    >




  6. #6
    Andy100
    Guest

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

    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" <[email protected]> wrote in message
    news:#[email protected]...
    > 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" <[email protected]> wrote in message

    news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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" <[email protected]> wrote in message

    > > news:[email protected]...
    > >> >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
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  7. #7
    Ron de Bruin
    Guest

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

    Hi Andy

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



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


    "Andy100" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:#[email protected]...
    >> 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" <[email protected]> wrote in message

    > news:[email protected]...
    >> > 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" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> 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" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> >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
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  8. #8
    Andy100
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Andy
    >
    > I like to see your workbook if possible
    > Send it to me private
    >
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Andy100" <[email protected]> wrote in message

    news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:#[email protected]...
    > >> 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" <[email protected]> wrote in message

    > > news:[email protected]...
    > >> > 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" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> 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" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> >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
    > >> >> >
    > >> >> >
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  9. #9
    Ron de Bruin
    Guest

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

    OK


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


    "Andy100" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi Andy
    >>
    >> I like to see your workbook if possible
    >> Send it to me private
    >>
    >>
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Andy100" <[email protected]> wrote in message

    > news:[email protected]...
    >> > 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" <[email protected]> wrote in message
    >> > news:#[email protected]...
    >> >> 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" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> > 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" <[email protected]> wrote in message
    >> >> > news:[email protected]...
    >> >> >> 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" <[email protected]> wrote in message
    >> >> > news:[email protected]...
    >> >> >> >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
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  10. #10
    Andy100
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > OK
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Andy100" <[email protected]> wrote in message

    news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> Hi Andy
    > >>
    > >> I like to see your workbook if possible
    > >> Send it to me private
    > >>
    > >>
    > >>
    > >> --
    > >> Regards Ron de Bruin
    > >> http://www.rondebruin.nl
    > >>
    > >>
    > >> "Andy100" <[email protected]> wrote in message

    > > news:[email protected]...
    > >> > 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" <[email protected]> wrote in message
    > >> > news:#[email protected]...
    > >> >> 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" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> > 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" <[email protected]> wrote in message
    > >> >> > news:[email protected]...
    > >> >> >> 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" <[email protected]> wrote in message
    > >> >> > news:[email protected]...
    > >> >> >> >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
    > >> >> >> >
    > >> >> >> >
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  11. #11
    Stefi
    Guest

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

    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" <[email protected]> wrote in message news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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" <[email protected]> wrote in message

    > > news:[email protected]...
    > >> >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
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
    >


  12. #12
    ewan7279
    Guest

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

    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" <[email protected]> wrote in message news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > >> 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > >> >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
    > > >> >
    > > >> >
    > > >>
    > > >>
    > > >
    > > >

    > >
    > >
    > >


  13. #13
    Stefi
    Guest

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

    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" <[email protected]> wrote in message news:[email protected]...
    > > > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > >> 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > >> >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
    > > > >> >
    > > > >> >
    > > > >>
    > > > >>
    > > > >
    > > > >
    > > >
    > > >
    > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1