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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
How is this code being triggered?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top