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