+ Reply to Thread
Results 1 to 8 of 8

Lookup value in column and copy all matching rows

  1. #1
    Registered User
    Join Date
    09-12-2016
    Location
    England, UK
    MS-Off Ver
    MS 365
    Posts
    59

    Lookup value in column and copy all matching rows

    Hello all you amazing people!

    I am really stuck on a piece of VBA code, meaning I have no idea where to start!

    What I have is a spreadsheet that can have up to 5,000 lines and 16 columns. In column E is a branch, and what I need to do is get the 3 or 4 matching branches in each of our groups to copy all matching rows into a separate spreadsheet.

    Please see attached example. What I need is for a piece of code to look in column E, find all rows where it says Branch 1, Branch 5 and Branch 9, and copy all of the rows of each three branches into tab A.

    I hope this makes sense, thank you in advance!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Re: Lookup value in column and copy all matching rows

    Hi !

    Quote Originally Posted by Ollie7957 View Post
    I have no idea where to start!
    Easy : use a filter or better an advanced filter !

  3. #3
    Registered User
    Join Date
    09-12-2016
    Location
    England, UK
    MS-Off Ver
    MS 365
    Posts
    59

    Re: Lookup value in column and copy all matching rows

    Thanks Marc, however this is going to another department and I need it to be automatic and quick for the end user.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Lookup value in column and copy all matching rows


    Filter and advanced filter are often used in VBA scripts, activate the Macro Recorder and see, at beginner level !

    See also the AdvancedFilter method in VBA inner help …

  5. #5
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Lookup value in column and copy all matching rows

    Hello Ollie,

    If its just the three branches that you're interested in, then try the following code assigned to a button:-

    Please Login or Register  to view this content.
    I hope that this helps.

    Cheerio,
    vcoolio.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Using an advanced filter that needs only a single codeline with real data !
    Here with the poor column E data, a second codeline must be added for a formula as criteria
    so just with a couple of codelines it's done …

  7. #7
    Registered User
    Join Date
    09-12-2016
    Location
    England, UK
    MS-Off Ver
    MS 365
    Posts
    59

    Re: Lookup value in column and copy all matching rows

    Thank you both, I think I have figured it out by combining part of Vcoolio's response with the autofiltering.

  8. #8
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Lookup value in column and copy all matching rows

    You're welcome Ollie.

    Cheerio,
    vcoolio.

+ 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. Compare entire rows from two sheets and copy any non-matching rows to a third sheet
    By outofthisworld in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2018, 05:42 PM
  2. [SOLVED] Need VBA code to lookup vaue through micr code copy matching rows to under group
    By johnmacpro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2016, 12:25 AM
  3. [SOLVED] Copy rows with matching first column value into another sheet
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-01-2015, 04:38 AM
  4. [SOLVED] Lookup Vlue and return all matching rows in order
    By TextMonkey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-24-2013, 03:03 PM
  5. Replies: 7
    Last Post: 02-02-2013, 07:34 AM
  6. Replies: 14
    Last Post: 03-16-2011, 11:40 AM
  7. Lookup criteria matching in rows and columns
    By JuJuBe in forum Excel General
    Replies: 2
    Last Post: 05-20-2010, 05:03 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