Hello all.
I need help with this code. I am trying to get this code to loop through each line copying the row information for the deposit that is listed as "RLCMO" or “RPCMO” in cell “H” in the SIGN_IN sheet to the sort sheet. I have tried to get it to loop but can’t seem to get it to go. the code is located in sub routine CAP_PD_DEPOSIT TRANSFER() in Module1. All help is greatly appreciated!
Public Sub Worksheet_Change(ByVal Target As Range)
Dim Srn As Integer 'Row number for SIGN_IN sheet.
Dim Sws As Worksheet 'Worksheet SIGN_IN.
Dim Crn As Integer 'Row number for CREDIT_CARD sheet.
Dim Cws As Worksheet 'Worksheet CREDIT_CARD.
Dim Hrn As Integer 'Row number for HP_DEPOSIT sheet.
Dim Hws As Worksheet 'Worksheet HP_DEPOSIT.
Dim Prn As Integer 'Row number for CAP_PD_DEPOSIT sheet.
Dim Pws As Worksheet 'Worksheet CAP_PD_DEPOSIT.
Dim Brn As Integer 'Row number for Bad_Tows sheet.
Dim Bws As Worksheet 'Worksheet Bad_Tows.
Dim Mrn As Integer 'Row number for 2017_Impounds sheet.
Dim Mws As Worksheet 'Worksheet 2017_Impounds.
Dim Drn As Integer 'Row number for DEPOSIT_SORT sheet.
Dim Dws As Worksheet 'Worksheet DEPOSIT_SORT.
Dim Trn As Integer 'Total number of rows to be checked for CreditCards, HP Deposits and CAP PD Deposits.
Dim Ssr As Integer 'Starting Row number for sheet being copied.
Dim Lp As Integer 'Loop counter for CreditCards, HP Deposits and CAP PD Deposits.
Dim Status As String 'String for "Completed - TRACS" on Deposits reports cell "B".
Dim I As Integer 'Used as counter in array processing.
Status = "Completed - TRACS" 'String for "Completed - TRACS" on Deposits reports cell "B".
Set Cws = Sheets("CREDIT_CARD") 'Worksheet CREDIT_CARD.
Set Hws = Sheets("HP_DEPOSIT") 'Worksheet HP_DEPOSIT.
Set Pws = Sheets("CAP_PD_DEPOSIT") 'Worksheet CAP_PD_DEPOSIT.
Set Bws = Sheets("Bad_Tows") 'Worksheet Bad_Tows.
Set Mws = Sheets("2017_Impounds") 'Worksheet 2017_Impounds.
Set Dws = Sheets("DEPOSIT_SORT") 'Worksheet DEPOSIT_SORT.
Const Fee As Integer = 150 'Fee is amount of Administration Fee.
Const CCFee As Integer = 152 'Fee is amount of Administration Fee plus $2.00 Transaction Fee for credit cards.
Dim CheckTotal As Integer 'Running total for all checks entered.
End Sub
Sub SaveWb()
ActiveWorkbook.Save 'Save workbook loop.
Application.OnTime Now + TimeValue("00:05:00"), "SaveWb" 'Set autosave time in minutes.
End Sub
Sub CAP_PD_DEPOSIT_TRANSFER()
'Start of data copy to CAP PD Deposit Report to DEPOSIT SORT sheet *************************************************************
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 11 Then
Ssr = 15
'Check sheet SIGN IN sheet for Total of rows to be checked.
Trn = (Sws.Cells(Sws.Rows.Count, "A").End(xlUp).Row) - (Ssr)
For Lp = 0 To Trn
'Check sheet for next empty row for copying
Drn = Dws.Cells(Dws.Rows.Count, "A").End(xlUp).Row + 1
'When entering data in a cell in SIGN_IN sheet Col K, If it has value of "RLMO" or "RPMO" Then copy cells _
'"I", "T", "R", "S" from SIGN_IN to CAP_PD_DEPOSIT Report Cells "A", "E", "F" enter Status String in cell _
'"B" and form date into "C".
If Target.Value = "RLCMO" Or Target.Value = "RPCMO" Then
Range("I" & Target.Row).Copy Dws.Range("A" & Drn) 'Copy & Paste = DR # from SIGN_IN Check 1
Range("B1").Copy Pws.Range("C" & Prn) 'Copy & Paste = Form Date from SIGN_IN Check 1
Range("T" & Target.Row).Copy Dws.Range("D" & Drn) 'Copy & Paste = Check Amount from SIGN_IN Check 1
Range("R" & Target.Row).Copy Dws.Range("E" & Drn) 'Copy & Paste = Check Name from SIGN_IN Check 1
Range("S" & Target.Row).Copy Dws.Range("F" & Drn) 'Copy & Paste = Check # from SIGN_IN Check 1
Range("S" & Target.Row).Copy Dws.Range("G" & Drn) 'Copy & Paste = Check # from SIGN_IN Check 1
Dws.Range("B" & Drn) = Status 'Copy & Paste = Status String Check 1
'End If
If Range("U" & Target.Row) = "" Then Exit Sub 'Checks for DR # in cell "U" if blank then exit sub
'Check sheet for next empty row for copying
Drn = Dws.Cells(Dws.Rows.Count, "A").End(xlUp).Row + 1
'When entering data in a cell in SIGN_IN sheet Col K, If it has value of "RLCMO" or "RPCMO" Then copy cells _
'"U", "X", "V", "W" from SIGN_IN to CAP_PD_DEPOSIT Report Cells "A", "E", "F" enter Status String in cell _
'"B" and form date into "C".
If Target.Value = "RLCMO" Or Target.Value = "RPCMO" Then
Range("U" & Target.Row).Copy Dws.Range("A" & Drn) 'Copy & Paste DR # from SIGN_IN Check 2
Range("B1").Copy Dws.Range("C" & Drn) 'Copy & Paste = Form Date from SIGN_IN Check 2
Range("X" & Target.Row).Copy Dws.Range("D" & Drn) 'Copy & Paste = From Check Amount from SIGN_IN Check 2
Range("V" & Target.Row).Copy Dws.Range("E" & Drn) 'Copy & Paste = From Check Name from SIGN_IN Check 2
Range("W" & Target.Row).Copy Dws.Range("F" & Prn) 'Copy & Paste = From Check # from SIGN_IN Check 2
Range("W" & Target.Row).Copy Pws.Range("G" & Drn) 'Copy & Paste = From Check # from SIGN_IN Check 2
Dws.Range("B" & Drn) = Status 'Copy & Paste = Status String Check 2
'End If
If Range("Y" & Target.Row) = "" Then Exit Sub 'Checks for DR # in cell "Y" if blank then exit sub
'When entering data in a cell in SIGN_IN sheet Col K, If it has value of "RLCMO" or "RPCMO" Then copy cells _
'"Y", "AB", "Z", "AA" from SIGN_IN to CAP_PD_DEPOSIT Report Cells "A", "E", "F" enter Status String in cell _
'"B" and form date into "C".
'Check sheet for next empty row for copying
Drn = Dws.Cells(Dws.Rows.Count, "A").End(xlUp).Row + 1
If Target.Value = "RLCMO" Or Target.Value = "RPCMO" Then
Range("Y" & Target.Row).Copy Dws.Range("A" & Drn) 'Copy & Paste = DR # From SIGN_IN Check 3
Range("B1").Copy Dws.Range("C" & Drn) 'Copy & Paste = Form Date from SIGN_IN Check 3
Range("AB" & Target.Row).Copy Dws.Range("D" & Drn) 'Copy & Paste = From Check Amount from SIGN_IN Check 3
Range("Z" & Target.Row).Copy Dws.Range("E" & Drn) 'Copy & Paste = From Check Name from SIGN_IN Check 3
Range("AA" & Target.Row).Copy Dws.Range("F" & Drn) 'Copy & Paste = From Check # from SIGN_IN Check 3
Range("AA" & Target.Row).Copy Dws.Range("G" & Drn) 'Copy & Paste = From Check # from SIGN_IN Check 3
Dws.Range("B" & Drn) = Status 'Copy & Paste = Status String Check 3
If Lp = 0 Then End If
Next Lp
End If
End Sub
Thanks again for all help on this.
Bookmarks