1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro to copy multiple rows based on selection

Discussion in 'Business Applications' started by MintC, Jun 8, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. MintC

    MintC Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    4
    Hi there guys

    Looking for help in excel 2007 for the macro part.

    Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

    Rgds, MintC
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi MintC,
    Welcome to the forum
    Just to see if I get the idea.

    I attached a jpg showing the resul of Copying and Pasting your data into the BOM sheet.

    Is this more or less what you expect?
     

    Attached Files:

  3. MintC

    MintC Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    4
    Hi Hans

    Thanks for the quick reply. Have checked your jpg file, This is what I have been looking for, its almost perfect. One small modification though. Is it possible to remove the CP1, CP2 & CP3 row, that would be perfect solution.

    Looking forward for help & appreciate all the help so far!

    Kind regards
    Manoj Tuteja
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    This was just copy an paste no code., I'll have to code it, so the dtata is OK but you don't want the CP1, CP2 etc?
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I do have to know how I know which customer buys which configuration, or will it alwasy be set as CP1 & CP2 or CP1 & CP3?

    There must be a list of how you start?
     
  6. MintC

    MintC Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    4
    Hi Hans

    Its little bit complicated. In reality these CP's are around 35. Customers can order any configuration. Actually customer does not need to know CP. These CP are our back end configurations based on what customers order. All they need to know is the list of items which is coming out from these CP's & which they have paid for.

    Based on discussion with customers & our sales people, in one of the cell in excel, we will will write down the CP numbers. It will not be fixed, it can be from CP1 to 35. Based on what customer has ordered, macro in excel will look for CP information in CP tab & paste the item list in the BOM tab. In reality a order from customer 1 may have include items from 5 CP's & for customer 2, it may include items from 25 CP's.

    Let me know incase of further query!

    Kind regards
    MintC
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I understand all that, but if you want a macro to figure out if a CP has been ordered then I will need to know sveral things:
    1. in which field the CPs belonging to each customer is stored
    2. how is it the filed filled, CP1 & CP2 OR CP1 & CP2
    3. is the cutsomer list and the BOM always on the same sheet?
    4. do you want a BOM sheet per customer?
    5. Do you want to be able to export each BOM sheet separately/

    etc.
     
  8. MintC

    MintC Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    4
    Hi Hans

    Sorry, was down with fever from last 3-4 days. Here is further info on some of the points raised above by you. Infact its good you have asked, since I have not thought about those. Sooner or later I would have to work on those.

    1. in which field the CPs belonging to each customer is stored

    Actually, it will be stored in cell B2 for first order, B3 for second order, B4 for third & B5 for fourth & B6 for fifth order. At the moment we will restrict to 5 orders only per customer. Otherwise data will become un-managable. Also only one customer per file.

    2. how is it the filed filled, CP1 & CP2 OR CP1 & CP2
    Fields in the column B will be populated something like this CP1, CP2, CP3. Separated by comma.

    3. is the cutsomer list and the BOM always on the same sheet?
    Practically, like to have on the same sheet.

    4. do you want a BOM sheet per customer?
    Actually we will use the whole excel only for one customer to keep it simple,

    5. Do you want to be able to export each BOM sheet separately/
    That could be a nice option. But I think not must at this point.

    Kind regards
    MintC
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Sorry it took a little while.

    I just did some coding, see if this is what you need,
    I'll explain later ow it works

    It's (almost) midnight here :)
     

    Attached Files:

  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    .... And ???
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1001122

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice