Copy Paste To Another Sheet Based On Value In Column

  • Thank you in advance to anyone that helps. I've been searching the forum for hours and I can't find what I need. I uploaded a zip file with the workbook I'm using. My workbook contains 3 sheets tilted Daily DCPMR Failures, Preventable Failures and No Arrival At Unit Scan.
    Based upon the sample data in the Daily DCPMR Failures sheet, I need a macro that will copy certain portions of the data from the Daily DCPMR Failures sheet into the other two sheets, based upon certain conditions.
    For the 1st condition, if you look in the Daily DCPMR Failures sheet, and go to B7, you will notice that it has a 03, if the next row below that witch would be B8 does not contain a 07, then I would want the data from A8, E8 and F8 copied and pasted into the 1st available blank row in the No Arrival At unit Sheet.


    The second condition would be if the date of delivery does not match the date it arrived at unit. For example, in the Daily DCPMR Failures sheet in cell D5, it arrived at unit on 11/27/2008(the time should be ignored) but it was delivered on 11/28/2007 cell D6.
    In this example since the day of delivery does not match, I would like the data from cells
    A6, E6 and F6 copied and pasted into the 1st available blank row in the Preventable failure sheet.


    I do this on a daily basis by looking at every single row of data and copying the data into the appropriate sheet. With several thousand rows to do, it takes hours and hours. Any help on this would greatly be appreciated.
    Thank you to all.

  • Re: Copy And Paste From One Sheet To Another Based Upon Certain Conditions


    Hello domgiants,You could try this. It determines the first available row in your destination sheets when you open your work book, then reviews your data when you call the routine "CopyAndPasteYourData".



    Perhaps not the most elegant way to determine the first available row in your destination sheets, but will get you moving in the first instance.



    Hope this helps.

  • Re: Copy Paste To Another Sheet Based On Value In Column


    domgiants,


    Question:
    "The second condition would be if the date of delivery does not match the date it arrived at unit."


    You do not always have/show per group of each "Label ID":
    ACCEPT OR PICKUP
    ARRIVAL AT UNIT
    DELIVERED


    Can you explain in further detail the second condition.



    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    Here is the code for the first condition:




    Have a great day,
    Stan

  • Re: Copy Paste To Another Sheet Based On Value In Column


    Thanks Matt.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Stan, the 1st part worked absolutely perfect, thank you so much!
    To answer your question. It may just be easier to explain what I'm doing. If you look in the Label ID column you'll see a bunch of numbers, the numbers actually represent delivery confirmation labels for parcels. When a mail man comes to deliver a package, he scans the number on the package. Customers are then able to go to USPS.com input the number and track their package. The "03" is a just a number that represents Accept or Pickup, which means when a customer mailed their package. The "07" represents Arrival At Unit, which means when the package arrived at the post office. There are actually 6 more codes for other things.
    "01" means delivered, the package was delivered.
    "02" means Notice left, the carrier attempted to deliver the package, but no one was home.
    "04" means refused, the customer refused the package.
    "05" means undeliverable as addressed, the address on the package is not valid.
    "06" means forwarded, the customer moved and the package was sent to the new address.
    "08" means missent, the package was sent to the wrong office.


    So what I want to have happen is this, the date the package arrives which would be a code "03", must match the date for the "01", "02", "04", "05", "06", "08" codes.


    (If it doesn't, the package is considered a preventable error, and the office gets a reprimand) I hope I answered your question, once again thank you very much.