+ Reply to Thread
Results 1 to 2 of 2

copy specific cells in row based of of value in anpther cell to another worksheet.

Hybrid View

COURTTROOPER copy specific cells in row... 11-22-2017, 07:41 PM
mjr veverka Re: copy specific cells in... 11-25-2017, 06:13 PM
  1. #1
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    copy specific cells in row based of of value in anpther cell to another worksheet.

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,249

    Re: copy specific cells in row based of of value in anpther cell to another worksheet.

    "Target" is a variable for worksheet event, not for Module procedures.

    If anything, it should be:
    For worksheet module:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        
        If Target.Column = 11 Then
            Call CAP_PD_DEPOSIT_TRANSFER(Target) 'Passing a "Target" object to the "CAP_PD_DEPOSIT_TRANSFER" procedure
        End If
    End Sub
    For "Module1" module:
    Sub CAP_PD_DEPOSIT_TRANSFER(rngTarget As Range) '"rngTarget" to not confuse with "Target"
        'Wherever "Target" occurs, replace it with "rngTarget", i.e.:
        If Target.Value = "RLCMO" Or Target.Value = "RPCMO" Then
        'to
        If rngTarget.Value = "RLCMO" Or rngTarget.Value = "RPCMO" Then
        
        'but
        
        If Target.Cells.Count > 1 Then Exit Sub 'remove it from here
        
        If Target.Column = 11 Then 'remove it from here
            'keep commands inside
        End If 'remove it from here
        
        'Correct the constructions "If ... Then ... End If" i.e. uncomment 'End If
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy specific cells in a row from one workbook to another based on Cell value
    By prvnchdry in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-12-2017, 03:44 AM
  2. [SOLVED] VBA code to copy specific cells based on cell value
    By Dineth in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-10-2016, 12:47 PM
  3. [SOLVED] Need to copy certain cells to different worksheet based on cell value
    By Lucille Boshoff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2016, 06:50 AM
  4. Replies: 7
    Last Post: 07-16-2013, 06:47 AM
  5. [SOLVED] Copy specific cells based on one word in the cell, to a different worksheet
    By Lfaulst1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2013, 05:26 PM
  6. Need to copy specific cells into an existing worksheet based on date entered by user
    By jrfleury in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2011, 09:44 AM
  7. Replies: 2
    Last Post: 11-20-2009, 02:05 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1