Issue with copy paste code

[ERed1]

Hi all,

I am having an issue with my copy paste code. Here is my 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.

 

[RoryA]

How is this code being triggered?

 

[ERed1]

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

 

[RoryA]

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:

         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]

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:

         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.

    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]

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]

How do I do that?

 

[RoryA]

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

 

[ERed1]

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?

 

[ERed1]

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?