Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Raleigh, North Carolina, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Search & Copy Macro (XLS2k SP3)

    Can someone help me figure out a macro to do this for me:

    I have a workbook that contains 2 sheets- Sheet 1 contains a master parts list of about 5000 parts and assorted descriptions, sorted by their part number in Column A. Sheet 2 contains an indented product breakdown listing for a particular product, with it's approximately 250 P/N (number format) and blue-print text description scattered over the sheet by rows. I need to produce a new Sheet 3, by searching Sheet 2 for any cell that contains only a part number (although it could be just every cell - speed is not a factor), then comparing that cell's contents with each cell in Column A on Sheet 1. If a match is found with any Column A cell on Sheet 1, that entire row is copied to the newly created Sheet 3, and the process continues until all occupied cells on Sheet 2 have been checked. What results on Sheet 3 is a unique BOM (Bill of Materials listing of only the P/N and descriptions) that are common to the master parts list and the parts that are used on that one individual product.

    I know this can be done with a macro, and in my limited macro experience, cannot get the results of the Macro Recorder to do what I want, even after I get in there and "Hack it Up!

    Any takers??? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Search & Copy Macro (XLS2k SP3)

    It would be helpful if you could post a small sample, so that we have an idea of what you're working with, and have something to test code on.

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Raleigh, North Carolina, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Search & Copy Macro (XLS2k SP3)

    You are absolutely right!
    Attached (I hope) is an zipped sample of the sheet 1 (limited) & 2 lists, as well as my hand-copied Sheet 3 that contains just 6 entries.

    Thanks for the prompt request!
    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Search & Copy Macro (XLS2k SP3)

    See the attached code. Don't forget to modify the sheet names as needed.

Posting Permissions

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