VBA Simple Copying one Range to Another Range

vba excel-2011
SparrwHawk picture SparrwHawk · Sep 21, 2011 · Viewed 41.6k times · Source

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

Answer

Alex P picture Alex P · Sep 21, 2011

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...

Related questions

Disable all dialog boxes in Excel while running VB script?

I have some code in VB that saves all XLSM files as XLSX. I already have the code that will do that for me, but dialog boxes show up for every action. This was fine for a few dozen files. …

Read More
How can I convert a range to a string (VBA)?

What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of …

Read More
Solver.xlam is missing?

I created a program in Excel 2010 on my PC that relies on the built-in solver for Excel. I made sure it worked on both Excel 2010 and Excel 2013 for PC. I then tried to use this same program on Excel 2011, and …

Read More