CODEWITHSUNDEEP
vbaexcelpaste
lizard053
lizard053

Reputation: 75

Excel VBA: Paste problems

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.

Upvotes: 4

Views: 12932

Answers (2)

L42
L42

Reputation: 19737

Explanation:

Cells.Offset(0,1).PasteSpecial

This will give Error 1004 since Cells refer to the entire sheet range and there is no way for you to offset it.

Cells(hotcell).Copy

This will give you the Error 5 if the value of hotcell is not numeric.
I think Cells only accepts numeric argument if you used above syntax.

How to use cells: (Excel 2007 and up versions)

1.Define R,C syntax:Cells(RowNumber, ColumnNumber)

Cells(1,1) 'refers to Range("A1")
Cells(1,2) 'refers to Range("B1")
Cells(2,1) 'refers to Range("A2")

2.Use a number only

Cells(1) 'refers to Range("A1")
Cells(2) 'refers to RAnge("B1") and so on
Cells(16385) 'refers to Range("A2")

3.Using Cells only

Cells.Copy 'copies the whole range in a sheet
Cells.Resize(1,1).Copy 'copies Range("A1")
Cells.Resize(1,1).Offset(0,1).Copy 'copies Range("B1")
Cells.Resize(2,1).Copy 'copies Range("A1:A2")

4.Using numbers and letters (This only works on Cells(RowNum, ColNum) syntax)

Cells(1, "A").Copy 'obviously copies A1
Cells(1, "A").Resize(2).Copy 'copies A1:A2

Now, how to copy and paste.
Suppose you want to copy A1:A5 and paste to the next column which is B.

Cells.Resize(5,1).Copy Cells.Resize(5,1).Offset(0,1)

The above will work because you Resize the Cells first before you do the Offset.
The values of A1:A5 will now be copied to B1:B5.
Hope this helps you.

Upvotes: 4

WGS
WGS

Reputation: 14179

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.

Upvotes: 5

Related Questions