VBA Simple Copying one Range to Another Range

Question

This is a really simple VBA formula but it's failing. It's only pasting into cell A6 onwards. Is it just me? Excel 2011 by the way.

Range("A4:A5").Select
Selection.Copy
Range("A6:A1000").Select
ActiveSheet.Paste

Do you mean it is only pasting into A6 and A7? I presume you are expecting it to paste (repeat) into A6 to A1000? What is in cells A4 and A5? — Alex P, Sep 21 '11 at 18:22

score 13 · Accepted Answer · answered Sep 21 '11 at 18:29

I think the issue is that you have two different values in A4 and A5 and so excel can only repeat those values in the paste range if the paste range is an even number of cells.

This works for me:

Range("A4:A5").Copy Destination:=Range("A6:A1001")

Note that A6:1001 is 996 cells (an even number). Using A6:A1000 is 995 and is an odd number so excel cannot work out how to repeat your values from A4 to A5.

I think this is the issue...but happy to be educated otherwise...

It works, fantastic, you saved me hours of head scratching. Many thanks — SparrwHawk, Sep 21 '11 at 19:21

VBA Simple Copying one Range to Another Range

1 Answers1