Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Copy Paste To Another Sheet Based On Value In Column

  1. #1
    Join Date
    20th December 2007
    Posts
    2

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    74

    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".

    VB:
    Public FailureRow As Integer 
    Public ArrivalRow As Integer 
     
    Sub auto_open() 
         
        Sheets("Daily DCPMR Failures").Select 
         
         'Set the first available row numbers for each of the destination sheets
        For FailureRow = 2 To 1000 
            If Sheets("Preventable Failures").Cells(FailureRow, 1) = "" Then Exit For 
        Next 
         
        For ArrivalRow = 2 To 1000 
            If Sheets("No Arrival At Unit Scan").Cells(ArrivalRow, 1) = "" Then Exit For 
        Next 
         
    End Sub 
     
    Sub CopyAndPasteYourData() 
        Dim RecordRow As Integer 
         
         'Scroll through your records and copy and paste to the appropriate sheet
        For RecordRow = 4 To 1000 
             
            Select Case Cells(RecordRow, 2).Value 
            Case "01" 
                Sheets("Preventable Failures").Cells(FailureRow, 1) = Cells(RecordRow, 1) 
                Sheets("Preventable Failures").Cells(FailureRow, 2) = Cells(RecordRow, 7) 
                Sheets("Preventable Failures").Cells(FailureRow, 3) = Cells(RecordRow, 6) 
                FailureRow = FailureRow + 1 'Increment the failure row to next blank row
            Case "03" 
                 'No action required.
            Case "07" 
                Sheets("No Arrival At Unit Scan").Cells(ArrivalRow, 1) = Cells(RecordRow, 1) 
                Sheets("No Arrival At Unit Scan").Cells(ArrivalRow, 2) = Cells(RecordRow, 7) 
                Sheets("No Arrival At Unit Scan").Cells(ArrivalRow, 3) = Cells(RecordRow, 6) 
                ArrivalRow = ArrivalRow + 1 'Increment the arrival row to next blank row
            Case Else 
                Exit Sub 
            End Select 
             
        Next 
         
         
    End Sub 
    
    
    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.
    Cheers,

    Matthew

  3. #3
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    670

    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:

    VB:
     
    Option Explicit 
    Sub MoveData() 
         
        Dim lngLastRowDaily As Long 
        Dim lngLastRowPF As Long 
        Dim lngLastRowNA As Long 
        Dim lngLoopCtr As Long 
         
        Application.ScreenUpdating = False 
         
         '1st condition
        With Sheets("Daily DCPMR Failures") 
            .AutoFilterMode = False 
            lngLastRowDaily = Range("A" & Rows.Count).End(xlUp).Row 
            For lngLoopCtr = 4 To lngLastRowDaily Step 1 
                If Cells(lngLoopCtr, "B") = "03" And Cells(lngLoopCtr + 1, "B") <> "07" Then 
                    lngLastRowNA = Sheets("No Arrival At Unit Scan").Range("A" & Rows.Count).End(xlUp).Row 
                    Range("A" & lngLoopCtr + 1).Copy 
                    With Sheets("No Arrival At Unit Scan").Range("A" & lngLastRowNA + 1) 
                        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
                        Application.CutCopyMode = False 
                    End With 
                    Range("E" & lngLoopCtr + 1 & ":F" & lngLoopCtr + 1).Copy 
                    With Sheets("No Arrival At Unit Scan").Range("B" & lngLastRowNA + 1) 
                        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
                        .HorizontalAlignment = xlCenter 
                        Application.CutCopyMode = False 
                    End With 
                End If 
            Next lngLoopCtr 
        End With 
         
        Application.ScreenUpdating = True 
         
    End Sub 
    
    

    Have a great day,
    Stan

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    20th December 2007
    Posts
    2

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

    Thanks Matt.
    Auto Merged Post;

    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.
    Last edited by domgiants; December 21st, 2007 at 02:04. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 8
    Last Post: November 28th, 2010, 20:54
  2. Copy Based On Column Criteria & Append To Another Sheet
    By columbo1977 in forum EXCEL HELP
    Replies: 20
    Last Post: January 25th, 2008, 17:07
  3. Copy Paste From Sheet Based On ListBox Choices
    By Alexon2008 in forum EXCEL HELP
    Replies: 10
    Last Post: January 15th, 2008, 11:18
  4. Copy Column To New Sheet Based On Conditions
    By brinaht in forum EXCEL HELP
    Replies: 1
    Last Post: April 3rd, 2007, 23:37
  5. Replies: 5
    Last Post: January 17th, 2007, 06:39

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
  •  
porno