Please Help Make This Paste Special

[rockyw]

This code works well but I need it to paste special. Can you help me with this? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("P:P,B:B")) Is Nothing Then
        With ThisWorkbook
            .Sheets("Sheet2").Range("E3:E24").Value = .Sheets("Sheet3").Range("E3:E24").Value
        End With
    End If
End Sub

 

[Rick Rothstein]

This should be the equivalent of what you posted using PasteSpecial. I'm guessing you will want to change the argument to the PasteSpecial line from xlPasteValues to one of the other available arguments (you didn't say which)...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("P:P,B:B")) Is Nothing Then
        With ThisWorkbook
            .Sheets("Sheet3").Range("E3:E24").Copy
            .Sheets("Sheet2").Range("E3:E24").PasteSpecial xlPasteValues
        End With
    End If
End Sub

 

[rockyw]

Thank you, I want to paste values only, do I need to change anythng for that. The one blank cell returned a 0, can this code be made to not returna 0 from an empty cell? Thanks

 

[Rick Rothstein]

Thank you, I want to paste values only, do I need to change anythng for that. The one blank cell returned a 0, can this code be made to not returna 0 from an empty cell? Thanks

No, for values only, the code I posted is all you need; but I don't understand why you asked to change your original code... direct assignment of values is faster than using Copy/Paste Special (although for only two cells, you won't ever see the difference in speed). As for your question about 0... I don't get that happening on my system... blank cells copy over as blank cells.

 

[rockyw]

The blanks have a formula but no value, is that why it returns a 0? Can we stop that. That was the problem with the other code.

 

[Rick Rothstein]

The blanks have a formula but no value, is that why it returns a 0? Can we stop that. That was the problem with the other code.

If the formula is returning the empty string (""), then it shouldn't convert to 0 when copied (at least it did not do it when I just tested it).