Copy a row from one spread sheet to another with conditions

  • Hey so im looking for a lil bit of help here,


    Early in the year i had some help create a bin log file for me on this site, eventually i realized i was missing one more function.


    Ok so in the 1st picture i provided "Main Sheet", i had the rows from A-L copied to the next available row in Outstanding Bins only if column "I" (Date Delivered) is filled and then column "k"(Date Collected) is filled in afterwards. You can see this in the second picture i provided for "Outstanding Bins".


    What i want to accomplish also is have rows from A-L copied to the next available row in "Completed jobs" only if column "I" (Date Delivered) is filled in, column "J" (Date Paid) is filled in, and then column "k"(Date Collected) is filled in afterwards.


    I Tried brainstorming this on my own and could not figure it out since im a complete noob, here is the code provided from the Main sheet Tab



    Option Explicit


    '''''''''''''''''''''''''''''''''''

    ' Version 6.2 - dated 11 Mar 2021 '

    '''''''''''''''''''''''''''''''''''


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' Use Mouse Left Double-Click to Sort Ascending on Any Header Title

    ' AND

    ' in Column Date Delivered ... it deletes data in 4 last Columns I to L

    If Target.Column > 12 Then Exit Sub

    If Target.Row = 2 Then

    Select Case Target.Column

    Case 1

    Application.Run ("CustomSort")

    Case 9 ' Red

    Call SortByColour1(Target)

    Case 10 ' Green

    Call SortByColour2(Target)

    Case 11 ' Yellow

    Call SortByColour3(Target)

    Case 12 ' Blue

    Call SortByColour4(Target)

    Case Else

    Call StandardSort(Target)

    End Select

    Else

    ' Whenever Column A (i.e. #1) - sheet Bins # is Double-Clicked '''''''''

    If Target.Column <> 1 Then Cancel = True: Exit Sub

    Dim x As Long: x = Target.Row

    ' Clear Contents in Columns B to L

    Range(Cells(x, 2), Cells(x, 12)).ClearContents

    ' Delete Interior Color

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = xlNone

    End If

    Cancel = True

    End Sub



    Private Sub Worksheet_Change(ByVal Target As Range)

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ' Objective of this Event Macro :

    ' to copy and auto-populate the next available row in Outstanding Bins sheet

    ' ONLY if date delivered (Column K) AND date collected (Column I) are filled BUT NO date paid (Column J) ...

    ' For the Overweight Fee (Column L), if filled, it will auto-fill the next available row in overweight sheet.

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    If Target.CountLarge > 1 Then Exit Sub

    If Target.Column > 12 Then Exit Sub

    Dim lRow As Long, j As Long

    Dim x As Long: x = Target.Row

    Dim y As Long: y = Target.Column


    ' Deal with Exceptions : If input is deleted from any of the Last 4 Columns ...

    ' Interior Color for Entire Row MUST be re-adjusted ....

    ' To be aligned with Conditional Formatting rule ....

    ' Revert Font Color from White to Black

    If y > 8 And IsEmpty(Target) Then

    With Range(Cells(x, 1), Cells(x, 12))

    .Font.Color = vbBlack

    .Font.Bold = False

    For j = 9 To 12

    If Not IsEmpty(Cells(x, j)) Then

    .Interior.Color = Cells(2, j).Interior.Color

    Exit For

    Else

    .Interior.Color = xlNone

    End If

    Next j

    End With

    Exit Sub

    End If

    ' Standard Process

    Select Case y

    Case 4

    ' Add Sales Tax 13% for Non-Cash Payments

    If Target <> "Cash" Then Target.Offset(0, -1) = Target.Offset(0, -1) * 1.13

    Case 6

    ' Add $15 for each Extra Day

    If Target >= 1 Then Target.Offset(0, -3) = Target.Offset(0, -3) + (15 * Target.Value)

    Case 9, 10

    '

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = Cells(2, y).Interior.Color

    Case 11

    ' Outstanding Bins

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = Cells(2, y).Interior.Color

    With Sheets("Outstanding Bins")

    lRow = .Columns(1).Find("*", After:=Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious).Row + 1

    ' No Date Paid and Yes Date Delivered

    If Target.Offset(, -1) = "" And IsDate(Target.Offset(0, -2)) Then

    Range(Cells(x, 1), Cells(x, 12)).Copy Destination:=.Range("A" & lRow)

    End If

    Target.Activate

    End With

    Case 12

    ' Overweight Bins

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = Cells(2, y).Interior.Color

    With Sheets("Overweight Bins")

    lRow = .Columns(1).Find("*", After:=Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious).Row + 1

    Range(Cells(x, 1), Cells(x, 12)).Copy Destination:=.Range("A" & lRow)

    Target.Activate

    End With

    End Select

    End Sub

  • You have 58 posts but don't seem to have read the rules.


    Read them then re-ask your question using code tags and I would suggest that attach an example workbook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!