Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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
    75

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

    Code:
    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
    695

    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:

    Code:
    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 03: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, 21:54
  2. Copy Based On Column Criteria & Append To Another Sheet
    By columbo1977 in forum EXCEL HELP
    Replies: 20
    Last Post: January 25th, 2008, 18:07
  3. Copy Paste From Sheet Based On ListBox Choices
    By Alexon2008 in forum EXCEL HELP
    Replies: 10
    Last Post: January 15th, 2008, 12:18
  4. Copy Column To New Sheet Based On Conditions
    By brinaht in forum EXCEL HELP
    Replies: 1
    Last Post: April 4th, 2007, 00:37
  5. Replies: 5
    Last Post: January 17th, 2007, 07: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