Excel VBA: Paste problems

excel, paste, vba

Solution

There are many ways to approach this kind of problem so I'll try to list some of the ones I use.

No-paste approach

Sub CP1()
    'This basically just transfers the value without fuss.
    Dim Rng As Range
    Set Rng = Range("A1")
    Rng.Offset(0,1) = Rng.Value
End Sub

Simple paste approach

Sub CP2()
    'This copies a cell exactly as it is.
    Dim Rng As Range
    Set Rng = Range("A1")
    Rng.Copy Rng.Offset(0,1) 'Read: Copy Rng to Rng.Offset(0,1).
    Application.CutCopyMode = False
End Sub

Special paste approach

Sub CP3()
    'This copies the format only.
    Dim sRng As Range, tRng As Range
    Set sRng = Range("A1")
    Set tRng = sRng.Offset(0, 1)
    sRng.Copy
    tRng.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
End Sub

Try determining from the three above which it is you want and modify accordingly. ;)

Hope this helps.

Problem

I have tried a variety of ways to do this paste, but none of them are working. I am extremely new to programming, so I need some help with understanding why I keep getting either error 1004 or 5. I don't even understand what these errors mean. ``` Cells(hotcell).Copy Cells.Offset(0, 1).PasteSpecial ``` or ...Paste, ...PasteSpecial = xlpasteall, ...pastespecial Paste:= xlpasteall, Range(Cells("B" & i)).paste, Range("B" & i).paste, and so on as above. I'm at a total loss. Everything else in the program is working just fine. I just can't get it to paste my copied values into the desired cells (all offset by a certain number of columns, but in the same row). Help and explanation both appreciated. Edit Thanks to BOTH of the answers I recieved, I was able to solve my problem. I really couldn't find a good answer anywhere I looked. Thank you! The solution I used was one of the simplest: ``` rng.Offset(0, 1) = rng.Text ``` Thanks again to the posters who answered, and the ones who commented. I was making it far too difficult.

Original source