Issue with copy paste code

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
How is this code being triggered?
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
It is being triggered by a button. A shape with a macro assigned to it.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
How do I do that?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 
Last edited:

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,101,756
Messages
5,482,698
Members
407,358
Latest member
Maze123

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top