Excel VBA: Paste problems

Question

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.

Welcome! This is not an answer, just a comment: often beginners try with copy and paste when a simpler Cells(R1, C1).Value = Cells(R2, C2).Value would do the job better and faster. Would that work for you? — stenci
– stenci, Commented Dec 10, 2013 at 4:04
When you use Cells you refer to the entire Range of a sheet and there is no way you can use Offset in it because there is nowhere to offset to. So you will get error 1004. To know ways on how to copy and paste, refer to BK201's post. It should get you started. — L42
– L42, Commented Dec 10, 2013 at 5:34
Also i'm curious whats the value of hotcell in your code. Cells i think accept numeric argument. If the value of hotcel is not a numeric value, you'll get error 5. see my post for the complete explanation — L42
– L42, Commented Dec 10, 2013 at 5:46
Thanks so much! hotcell was defined as a string, since it is a letter. I tried to get help from my other half, but out of the dozen or so languages he knows, VB isn't important enough for his work. LOL. — lizard053
– lizard053, Commented Dec 10, 2013 at 12:55

WGS · Accepted Answer · 2013-12-10 05:12:15Z

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.

L42 · Accepted Answer · 2014-08-06 22:43:28Z

4

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.

edited Aug 6, 2014 at 22:43

answered Dec 10, 2013 at 6:00

L42

19.8k11 gold badges47 silver badges70 bronze badges

1 Comment

lizard053 Over a year ago

Thank you so much for all the info! It really has helped a ton.

Collectives™ on Stack Overflow

Excel VBA: Paste problems

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related