Private Sub Button15_Click() 'row 4
Dim KIP As Worksheet, COMPLETED As Worksheet
Set KIP = Sheet1
Set COMPLETED = Sheet17
'CREATES AND SETS THE VARIABLES THAT WILL BE USED AS REFERENCES FOR THE CODE TO IDENTIFY EACH VALUE ON THE SHEET'
Dim LOC As Range, REPD As Range, APP As Range, VIN As Range
Dim VEH As Range, CTL As Range, LHD As Range, KT As Range
Dim VDR As Range, KC As Range, DTC As Range, CST As Range
Dim BLD As Range, INV As Range
'THESE ARE NON TRANSFER RANGES INCLUDED BECAUSE THEY ARE USED IN CLEAR CONTENTS ROUTINE
Dim DKC As Range, OL As Range, KAD As Range, DLC As Range
'EACH OF THESE SHOULD CORRELATE WITH THE SAME ORDER THEY APPEAR ON THE SPREADSHEET'
Set LOC = KIP.Range("D4") 'LOCATION
Set REPD = KIP.Range("E4") 'REPO DATE
'F,G,H CALCULATION COLUMNS
Set APP = KIP.Range("I4") 'DAYS TILL KEY APPROVED
Set VIN = KIP.Range("J4") 'VIN
Set VEH = KIP.Range("K4") 'VEHICLE DESCRIPTION
Set CTL = KIP.Range("L4") 'CLIENT
Set LHD = KIP.Range("M4") 'LEINHOLDER
Set KT = KIP.Range("N4") 'KEY TYPE
'O CALUCULATION COLUMN
Set VDR = KIP.Range("P4") 'VENDOR
Set KC = KIP.Range("Q4") 'KEYCODE
'R CALCULATION COLUMN
Set DTC = KIP.Range("S4") 'DAYS TO COMPLETE KEY
Set CST = KIP.Range("T4") 'COST TO CREATE KEY
Set BLD = KIP.Range("U4") 'COST BILLED TO CLIENT
Set INV = KIP.Range("V4") 'INVOICE NUMBER
'END OF TRANSFERRED DATA FOLLOWING ARE NOT IN ORDER
Set DKC = KIP.Range("R4") 'DATE KEY WAS COMPLETE - NEEDS TO BE CLEARED
Set OL = KIP.Range("H4") 'DAYS ON LOT - NEEDS TO BE CLEARED
Set KAD = KIP.Range("G4") 'DATE KEY APPROVED - NEEDS TO BE CLEARED
Set DLC = KIP.Range("O4") 'DATE KEY REQUESTED FROM VENDOR - NEEDS TO BE CLEARED
'SEQUENCE BELLOW DIRECTS TRANSFER TO FIRST DEST SELL LOCATION C4 ON COMPLETED
Dim DestCell As Range
If COMPLETED.Range("C4") = "" Then
Set DestCell = COMPLETED.Range("C4")
Else
Set DestCell = COMPLETED.Range("C4").End(xlDown).Offset(1, 0)
'BELOW IS CODE FOR MESSAGE BOX
End If
If VIN = "" Or INV = "" Then
MsgBox "EITHER VIN OR INVOICE INFORMATION IS MISSING. PLEASE UPDATE AND RETY (NO DATA HAS BEEN TRANSFERRED)"
Exit Sub
End If
'DEST CELL IS CELL C4 SO ALL CELLS AFTER THIS ARE OFFSET BY ONE LINE SO REPD IS ONE CELL RIGHT OF DEST CELL C4'
LOC.Copy DestCell
REPD.Copy DestCell.Offset(0, 1)
APP.Copy DestCell.Offset(0, 11).Value = APP.Value
VIN.Copy DestCell.Offset(0, 2)
VEH.Copy DestCell.Offset(0, 3)
'LOCATION (0,4)(0,5) IS VEHICLE MILEAGE/RUN STATUS'
CTL.Copy DestCell.Offset(0, 6)
LHD.Copy DestCell.Offset(0, 7)
KT.Copy DestCell.Offset(0, 9)
VDR.Copy DestCell.Offset(0, 8)
KC.Copy DestCell.Offset(0, 10)
'LOCATION (0,11) IS KEY DAYS STORED
DTC.Copy DestCell.Offset(0, 12)
CST.Copy DestCell.Offset(0, 14)
BLD.Copy DestCell.Offset(0, 15)
INV.Copy DestCell.Offset(0, 16)
'CLEARS THE CONTENTS OF THE LINES ON THE KIP ONCE TRANSFER IS COMPLETE'
LOC.ClearContents
REPD.ClearContents
APP.ClearContents
VIN.ClearContents
VEH.ClearContents
CTL.ClearContents
LHD.ClearContents
KT.ClearContents
VDR.ClearContents
KC.ClearContents
DTC.ClearContents
CST.ClearContents
BLD.ClearContents
INV.ClearContents
'NON TRANSFER CELLS
DKC.ClearContents
OL.ClearContents
KAD.ClearContents
DLC.ClearContents
End Sub
