+ Reply to Thread
Results 1 to 6 of 6

Copy Selected cells to another sheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    Copy Selected cells to another sheet

    I really need it to get a combined code to two sets of values, all have the same columns to copy from but when copying to deposit the values of "RLCMO", Or "RPCMO" Or "RLCMOEC" Or "RPCMOEC" to place the value of columns “U”, “X”, and “AA” into Deposit Column “E”. Copy columns “U”, “X”, and “AA” into Deposit Column “D” for column “K” values of "RLDMO", "RLNDMO", "RLDMOEC", "RLNDMOEC", "RPDMO”, RPNDMO”, ‘RPDMOEC", "RPNDMOEC”. I am not sure how to do it so I was trying to get the first part to run in a code then I could change the appropriate columns to run the other set of values. Data in columns “I” on Sign to “A” on Deposit, “COMPLETED – TRACS” on Deposit “B”, “J” on Sign to Deposit “C”, “S” on Sign to Deposit “” F” and “T” on Sign to Deposit “G” on the first run. The second time through if there is data in column “Y” the copy cells are the same for the first three copying and only the Sign cells change to Cells “V” and “W” for Deposit columns “V” and “W” to Deposit columns “F” and “G” and on third run Sign columns are “Y” and “Z” to Deposit columns “F” and “G”. Sign columns “U”, “X” and “AA” to Deposit column “E”.

    I have tried to get a for next loop to work by itself but can’t get it to work. I also tired this code but the statement of checking the last row on Sign
    And stopping it if it is equal to or larger that the last row in use on sign. I have stepped through it and on the first time it hits but it just shows that Kcol is 5 and LastRow is 22 then exits the sub and starts over at the first row again.

    This is what I tried without success. I can’t figure out why I can’t get this code working.

    Sub Cap_Dep()
    
    Dim Deposit As Worksheet: Set Deposit = Worksheets("Deposit")
    Dim Sign As Worksheet:    Set Sign = Worksheets("Sign")
    Dim Credit As Worksheet:  Set Credit = Worksheets("Credit")
    Dim Directory As Worksheet:   Set Directory = Worksheets("Directory")
    
    
    Dim LastRow As Long
    Dim Kcol As Long
    Dim Drow As Long
    
    Sign.Activate
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    'Deposit.Activate
    Drow = 13
    
        For Kcol = 5 To LastRow
            GoTo Srns
    Tsr:
         Next Kcol
        
    Srns:
                   'If Sign.Range("K" & Kcol).Value = "RLCMO" Or Sign.Range("K" & Kcol).Value = "RPCMO" Or Sign.Range("K" & Kcol).Value = "RLCMOEC" Or Sign.Range("K" & Kcol).Value = "RPCMOEC" Then
    If Sign.Range("K" & Kcol).Value = "RLCMO" Or Sign.Range("K" & Kcol).Value = "RPCMO" Or Sign.Range("K" & Kcol).Value = "RLCMOEC" Or Sign.Range("K" & Kcol).Value = "RPCMOEC" Then
        Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
        Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
        Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
        Deposit.Range("E" & Drow).Value = Sign.Range("U" & Kcol).Value
        Deposit.Range("F" & Drow).Value = Sign.Range("S" & Kcol).Value
        Deposit.Range("G" & Drow).Value = Sign.Range("T" & Kcol).Value
            If Sign.Range("V" & Kcol).Value = "" Then
                GoTo Tsub
                    Else
                Drow = Drow + 1
                    Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
                    Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
                    Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
                    Deposit.Range("E" & Drow).Value = Sign.Range("X" & Kcol).Value
                    Deposit.Range("F" & Drow).Value = Sign.Range("V" & Kcol).Value
                    Deposit.Range("G" & Drow).Value = Sign.Range("W" & Kcol).Value
                        If Sign.Range("Y" & Kcol).Value = "" Then
                            GoTo Tsub
                                Else
                            Drow = Drow + 1
                                Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
                                Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
                                Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
                                Deposit.Range("E" & Drow).Value = Sign.Range("AA" & Kcol).Value
                                Deposit.Range("F" & Drow).Value = Sign.Range("Y" & Kcol).Value
                                Deposit.Range("G" & Drow).Value = Sign.Range("Z" & Kcol).Value
                            Drow = Drow + 1
                    End If
                End If
            End If
            If Kcol <= LastRow Then
                Exit Sub
    Tsub:
    GoTo Tsr
    Tstop:
    End If
    End Sub
    All Help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Selected cells to another sheet

    Try changing:

    If Kcol <= LastRow Then
    to:

    If Kcol >= LastRow Then
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    Re: Copy Selected cells to another sheet

    xladept

    Thanks that makes it loop through now but it doesn't copy all the row data. when I step it through some times it will pickup the first check info for 75. but won't pickup the second one in the same row and won't pick up a single entry for 150.00 then the next time it will. I see no reason for it to jump over it.
    I have Added Enable.Events = false thinking that was the problem but it changed nothing.
    Do you see any reason for the arbitrary coping in my code?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Selected cells to another sheet

    You're welcome and thanks for the rep - glad it's solved!

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copy Selected cells to another sheet

    See if this works:

    Sub Cap_Dep()
    
    Dim Deposit As Worksheet: Set Deposit = Worksheets("Deposit")
    Dim Sign As Worksheet:    Set Sign = Worksheets("Sign")
    Dim Credit As Worksheet:  Set Credit = Worksheets("Credit")
    Dim Directory As Worksheet:   Set Directory = Worksheets("Directory")
    
    
    Dim LastRow As Long
    Dim Kcol As Long
    Dim Drow As Long
    
    
    LastRow = Sign.Cells(Rows.Count, 1).End(xlUp).Row
    
    Drow = 13
    
        For Kcol = 5 To LastRow
            GoTo Srns
    Tsr:
        Drow = Drow + 1: Next Kcol
        
    Srns:
                  
    If Sign.Range("K" & Kcol) = "RLCMO" Or Sign.Range("K" & Kcol) = "RPCMO" Or _
        Sign.Range("K" & Kcol) = "RLCMOEC" Or Sign.Range("K" & Kcol) = "RPCMOEC" Then
        Deposit.Range("A" & Drow) = Sign.Range("I" & Kcol)
        Deposit.Range("B" & Drow) = "COMPLETED-TRACS"
        Deposit.Range("C" & Drow) = Sign.Range("J" & Kcol)
        Deposit.Range("E" & Drow) = Sign.Range("U" & Kcol)
        Deposit.Range("F" & Drow) = Sign.Range("S" & Kcol)
        Deposit.Range("G" & Drow) = Sign.Range("T" & Kcol)
            If Sign.Range("V" & Kcol) = "" Then
                GoTo Tsub
                    Else
                'Drow = Drow + 1
                    Deposit.Range("A" & Drow) = Sign.Range("I" & Kcol)
                    Deposit.Range("B" & Drow) = "COMPLETED-TRACS"
                    Deposit.Range("C" & Drow) = Sign.Range("J" & Kcol)
                    Deposit.Range("E" & Drow) = Sign.Range("X" & Kcol)
                    Deposit.Range("F" & Drow) = Sign.Range("V" & Kcol)
                    Deposit.Range("G" & Drow) = Sign.Range("W" & Kcol)
                        If Sign.Range("Y" & Kcol) = "" Then
                            GoTo Tsub
                                Else
                            'Drow = Drow + 1
                                Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
                                Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
                                Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
                                Deposit.Range("E" & Drow).Value = Sign.Range("AA" & Kcol).Value
                                Deposit.Range("F" & Drow).Value = Sign.Range("Y" & Kcol).Value
                                Deposit.Range("G" & Drow).Value = Sign.Range("Z" & Kcol).Value
                            'Drow = Drow + 1
                    End If
                End If
            End If
            If Kcol >= LastRow Then
                Exit Sub
    Tsub:
    GoTo Tsr
    Tstop:
    End If
    End Sub

  6. #6
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    Re: Copy Selected cells to another sheet

    Thank you very much! I was just coming back to up date my problem.
    I had just found my problem. Both yours and mine work so I am going to post my version as well.

    Sub Cap_Dep()
    
    Dim Deposit As Worksheet: Set Deposit = Worksheets("Deposit")
    Dim Sign As Worksheet:    Set Sign = Worksheets("Sign")
    Dim Credit As Worksheet:  Set Credit = Worksheets("Credit")
    Dim Directory As Worksheet:   Set Directory = Worksheets("Directory")
    Dim LastRow As Long
    Dim Kcol As Long
    Dim Drow As Long
    
       Application.EnableEvents = False                                                                    'Turns off to save memory issues.
    
    Sign.Activate
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Drow = 13
    Deposit.Activate
        For Kcol = 5 To LastRow
            GoTo Srns
    Tsr:
         Next Kcol
    Srns:
    'If Sign.Range("K" & Kcol).Value = "RLCMO" Or Sign.Range("K" & Kcol).Value = "RPCMO" Or Sign.Range("K" & Kcol).Value = "RLCMOEC" Or Sign.Range("K" & Kcol).Value = "RPCMOEC" Then
    If Sign.Range("K" & Kcol).Value = "RLCMO" Or Sign.Range("K" & Kcol).Value = "RPCMO" Or Sign.Range("K" & Kcol).Value = "RLCMOEC" Or Sign.Range("K" & Kcol).Value = "RPCMOEC" Then
        Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
        Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
        Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
        Deposit.Range("D" & Drow).Value = "Not Capitol" 'Sign.Range("J" & Kcol).Value
        Deposit.Range("E" & Drow).Value = Sign.Range("U" & Kcol).Value
        Deposit.Range("F" & Drow).Value = Sign.Range("S" & Kcol).Value
        Deposit.Range("G" & Drow).Value = Sign.Range("T" & Kcol).Value
        Drow = Drow + 1
            If Sign.Range("V" & Kcol).Value = "" Then
                GoTo Tsub
                    Else
                    Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
                    Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
                    Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
                    Deposit.Range("D" & Drow).Value = "Not Capitol" 'Sign.Range("J" & Kcol).Value
                    Deposit.Range("E" & Drow).Value = Sign.Range("X" & Kcol).Value
                    Deposit.Range("F" & Drow).Value = Sign.Range("V" & Kcol).Value
                    Deposit.Range("G" & Drow).Value = Sign.Range("W" & Kcol).Value
                    Drow = Drow + 1
                        If Sign.Range("Y" & Kcol).Value = "" Then
                            GoTo Tsub
                                Else
                                Deposit.Range("A" & Drow).Value = Sign.Range("I" & Kcol).Value
                                Deposit.Range("B" & Drow).Value = "COMPLETED-TRACS"
                                Deposit.Range("C" & Drow).Value = Sign.Range("J" & Kcol).Value
                                Deposit.Range("D" & Drow).Value = "Not Capitol" 'Sign.Range("J" & Kcol).Value
                                Deposit.Range("E" & Drow).Value = Sign.Range("AA" & Kcol).Value
                                Deposit.Range("F" & Drow).Value = Sign.Range("Y" & Kcol).Value
                                Deposit.Range("G" & Drow).Value = Sign.Range("Z" & Kcol).Value
                            Drow = Drow + 1
                    End If
                End If
            End If
        If Kcol >= LastRow Then
            Exit Sub
                Else
            GoTo Tsub
    Tsub:
    GoTo Tsr
    Tstop:
           Application.EnableEvents = True                                                                    'Turns off to save memory issues.
    End If
    End Sub
    Thanks again for all your help and patience!
    Have a great day!

+ 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. VBA-Copy selected cells and paste under table on another sheet
    By jsherwood in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2018, 04:46 AM
  2. Replies: 7
    Last Post: 10-21-2015, 02:07 PM
  3. Replies: 8
    Last Post: 09-03-2015, 08:43 AM
  4. [SOLVED] Copy Value from selected cells to a new workbook sheet
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2013, 11:33 AM
  5. copy selected cells to new sheet
    By sad212 in forum Excel General
    Replies: 0
    Last Post: 06-11-2012, 02:45 PM
  6. copy selected cells to new sheet in a row using macro
    By cvsuryam in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2011, 04:11 PM
  7. Replies: 2
    Last Post: 08-08-2006, 12:50 PM

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