+ Reply to Thread
Results 1 to 12 of 12

Search for keywords and copy rows containing keywords to new sheet

  1. #1
    Registered User
    Join Date
    10-18-2012
    Location
    toncul
    MS-Off Ver
    Excel 2003
    Posts
    8

    Search for keywords and copy rows containing keywords to new sheet

    Hi
    I have attached a excel file showing a list (3 column, 12 rows).Book_Ex.xlsx
    This is short version of my "real" excel sheet that has over 3500 rows.
    I would like to make a macro which could scan the sheet for keywords. Let's say just 3 words for the sake of the example: "Structural", "fatigue" and "ULS". Only column 3 need to be scanned for keywords.
    I would like the macro to select the entire row containing the keyword(s) and copy them to a new excel sheet (sheet2) while the other rows (not containing the keywords) shall be copied to another sheet (sheet3).
    I am not very good a programming so I would very much appropriate some help.
    Thanks.
    Regards.
    Last edited by lenorsk; 10-15-2013 at 04:33 AM.

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Search for keywords and copy rows containing keywords to new sheet

    Hi Lenorsk,

    Please have a look at the attached workbook. Note that you will have to change the worksheet names to match your final workbook
    Attached Files Attached Files
    If you like my contribution click the star icon!

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for keywords and copy rows containing keywords to new sheet

    Another one
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    04-25-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Search for keywords and copy rows containing keywords to new sheet

    Ollie and AB beat me to it, but here's another one:
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-18-2012
    Location
    toncul
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Search for keywords and copy rows containing keywords to new sheet

    thanks to you guys.

    AB33: your macro works very well with the example but it fails when I copy it to my real excel file with 3500 rows. The data actually starts from row 5 in my real excel sheet. The debug highligths the line If x(i,3) like ... and says x(i,3)=<subscript out of range>.
    If I change to i=5 instead of i=2 as the list starts from row 5 then I get another bug at the line .Range("A2").Resize(n, UBound(y,2))=y

    OllieB: In the example sheet I have 2 lines containing the word "Structural" but only one of them is copied.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for keywords and copy rows containing keywords to new sheet

    It does not matter which row you start since we are testing for each row for a condition.
    I know why the code fails.
    My assumption was you have data starting from row 1

    Change this line from a1 to a5

    Please Login or Register  to view this content.

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for keywords and copy rows containing keywords to new sheet

    If you have gaps in your data, you can use this line, instead.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Search for keywords and copy rows containing keywords to new sheet

    lenorsk,

    I have tested the code and it works fine.

    You might want to 'tweak' the parameters of the find instruction (case senstitive yes/no, match full cell value, etc) to tailor the search to your needs. Since you did not specify any requirements I left the normal defaults in place

  9. #9
    Registered User
    Join Date
    10-18-2012
    Location
    toncul
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Search for keywords and copy rows containing keywords to new sheet

    Thanks djdjdj. it works for both the example and the bigger file. I had to change For Each cell In Range("c2:c" & lastrow) to For Each cell In Range("c5:c" & lastrow)

    I forgot that but how would do if one would like to have the corresponding index nr from sheet 1 (nr in the grey column in the left of the sheet) copied together with the lines in sheet2 and sheet3. this would basically add a column in sheet 2 and sheet3. This would be nice for me to be able to go back on sheet1 and check.

    thanks a lot

  10. #10
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Search for keywords and copy rows containing keywords to new sheet

    lenorsk,

    oops, you were right - there was a mistake. New/corrected version attached. Sorry
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    04-25-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Search for keywords and copy rows containing keywords to new sheet

    Hi again,

    Same thing but with added column for "Index Number"
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-18-2012
    Location
    toncul
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Search for keywords and copy rows containing keywords to new sheet

    thanks guys. it works fine now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  2. [SOLVED] How to search across 2 columns for keywords from a list and copy each row to a new sheet?
    By keithtran12 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-01-2013, 05:16 AM
  3. search multiple columns and count number of rows the keywords appear
    By vincegc8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2013, 02:01 PM
  4. Set of keywords to search and move whole rows to another tab
    By lxdeb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2012, 03:17 PM
  5. Macro to Search and Copy Multiple Keywords into Another Sheet
    By Bangarang in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-05-2010, 01:57 PM

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