Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Issue with copy paste code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Issue with copy paste code

    Hi all,

    I am having an issue with my copy paste code. Here is my code:
    Code:
            If Range("B49").Value = "Other" Then            Sheets("Sheet1").Range("B50").Copy
                Sheets("Sheet2").Activate
                lastrow = Range("D65536").End(xlUp).Row
                Cells(lastrow + 1, 32).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            ElseIf Range("B49").Value = "Black" Then
                Sheets("Sheet1").Range("B49").Copy
                Sheets("Sheet2").Activate
                lastrow = Range("D65536").End(xlUp).Row
                Cells(lastrow + 1, 32).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            End If
    So I have a drop down in cell B49. The options are Black and Other. If other is selected then B50 appears and you can type a color in. This code constantly pastes nothing into the cell I have it pasting in. Any ideas on what could be causing this?

    Thanks for all the help.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Issue with copy paste code

    How is this code being triggered?

  3. #3
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with copy paste code

    It is being triggered by a button. A shape with a macro assigned to it.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Issue with copy paste code

    Assuming the cell definitely contains "Other" and not, say "other", and that you've checked the code is not pasting further down the sheet than you think it should, perhaps try this version instead of yours:

    Code:
             If Range("B49").Value = "Other" Then
                With Sheets("Sheet2")
                 .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row + 1, 32).Value = Sheets("Sheet1").Range("B50").Value
                End With
             ElseIf Range("B49").Value = "Black" Then
                With Sheets("Sheet2")
                 .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row + 1, 32).Value = Sheets("Sheet1").Range("B49").Value
                End With
             End If

  5. #5
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with copy paste code

    Quote Originally Posted by RoryA View Post
    Assuming the cell definitely contains "Other" and not, say "other", and that you've checked the code is not pasting further down the sheet than you think it should, perhaps try this version instead of yours:

    Code:
             If Range("B49").Value = "Other" Then
                With Sheets("Sheet2")
                 .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row + 1, 32).Value = Sheets("Sheet1").Range("B50").Value
                End With
             ElseIf Range("B49").Value = "Black" Then
                With Sheets("Sheet2")
                 .Cells(.Cells(.Rows.Count, "D").End(xlUp).Row + 1, 32).Value = Sheets("Sheet1").Range("B49").Value
                End With
             End If
    This did not work sadly. Here is a clip of my code. I simply repeat this for each cell/group of cells.
    Code:
        If Range("B17").Value = "Animal" Then
            
            Sheets("Sheet1").Range("D56:D58").SpecialCells(xlCellTypeVisible).Copy
            Sheets("Sheet2").Activate
            lastrow = Range("D65536").End(xlUp).Row
            Cells(lastrow + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    
            Sheets("Sheet1").Range("D18").Copy
            Sheets("Sheet2").Activate
            lastrow = Range("D65536").End(xlUp).Row
            Cells(lastrow + 1, 6).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        End If
    I repeat that for however many cells I have. Could that be one reason why this isn't working?

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Issue with copy paste code

    I'd suggest you try putting breakpoints in the code to check whether the lines you think should be running actually are, and where the values are being pasted.

  7. #7
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with copy paste code

    How do I do that?

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Issue with copy paste code

    Select the relevant line in the code and then press f9 (press again to turn it off when done). When the code runs, it will stop at that line if it gets to it.

    This is a worthwhile read: http://www.cpearson.com/Excel/DebuggingVBA.aspx

  9. #9
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with copy paste code

    Quote Originally Posted by RoryA View Post
    Select the relevant line in the code and then press f9 (press again to turn it off when done). When the code runs, it will stop at that line if it gets to it.

    This is a worthwhile read: http://www.cpearson.com/Excel/DebuggingVBA.aspx
    So I should insert your code from before into mine and do this to test?

  10. #10
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with copy paste code

    Quote Originally Posted by RoryA View Post
    I'd suggest you try putting breakpoints in the code to check whether the lines you think should be running actually are, and where the values are being pasted.
    So I have done this for a while now and I still have no luck with that code activating. The rest of the code works fine. Do you by chance have any more ideas on what I could try to get the code to work?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •