What is the "correct" way to copy-paste data in VBA?

Question

What is the "correct" way to copy-paste data in VBA in Excel?

Now, at least to my knowledge, these three are the only available methods:

the (Range) .Copy([Destination]) method
the (Worksheet) .Paste([Destination, Link]) method
and the (Range) .PasteSpecial([Paste], [Operation], [SkipBlanks], [Transpose]) method

Now, I did my research, these are the pros & cons of .Copy([Destination]) and the .Paste([Destination, Link]) method, least those, that I can think of:

pros:

allows us to paste data in the same expression as copying (code-readability I guess..?)

cons:

Cell references are a complete no go!
Your formatting and formulas might get messed up
If the range overlaps it will display a prompt, effectively stopping macro dead in its tracks (that's a huge bummer especially if you're trying to automize something)
Worse yet, if you press Cancel on the prompt, it will throw an Error** (unless handled)

On the other side of the coin, .PasteSpecial()

pros:

PasteSpecial() allows us to paste a specific part of range!
it allows us to specify what type of paste we want to do
has an inbuilt skipBlanks and transpose functionality
is not "so" Error-prone!

and I struggled to come up with any, but:

cons:

a bit more characters to write?

Now, that leads me to believe, that the Destination argument of .Copy() method should essentially be ignored and PasteSpecial() should be always used instead.

Is there ever an occassion where usage of it might be preferable over .PasteSpecial()? Or should PasteSpecial() be the norm for every copy-paste operation?

You forgot the assign value directly: `Range().Value = Range().Value` — Scott Craner, Aug 13 '18 at 16:37
Aren't you ignoring the [Worksheet.Paste Method](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-paste-method-excel)? — , Aug 13 '18 at 16:39
@ScottCraner I did think about it, but that's not really copy-pasting, but more of a typical pointer referencing, hence I decided not to include it in my question. I didn't want to open a book to the "what counts as copy-pasting" discussion. — Samuel Hulla, Aug 13 '18 at 16:39
@Jeeped yes, I did infact forget it. Though *(luckily for me)* I feel like the `.Paste` method contains very similar design flaws (if you could call it that way) as the `.Copy` method. Edited accordingly, thanks for the quick notice — Samuel Hulla, Aug 13 '18 at 16:43
With `PasteSpecial` it will throw an error if nothing is in the clipboard, so I think from my experience it needs error more handling. (especially if you are creating a Crlt+Shift+V shortcut to paste values!) — Profex, Aug 13 '18 at 16:46
"to settle the discussion once and for all" - um... good luck with that! I see nothing wrong with `.Copy()` if that fits the need. — Tim Williams, Aug 13 '18 at 16:48
@TimWilliams okay I admit, the more stylistic writer in me got carried away with that statement, while it sound cool, it has no rightful place in that question. Edited it out. — Samuel Hulla, Aug 13 '18 at 16:50
I almost never use `PasteSpecial`: most of the time `Copy` does exactly what I need, and is more concise. — Tim Williams, Aug 13 '18 at 16:51
if all you want is values, then nothing will beat performance of direct value assignment. This is the last example in my accepted answer here: https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another/19352099#19352099 — David Zemens, Aug 13 '18 at 16:53
I have to admit, trying to think of where/when I've used either in the past and the answer is, I rarely use either. I usually just copy the range to a variant, process the data and dump it back. If I need formatting, I'll do that after, but en masse. — Profex, Aug 13 '18 at 16:55
NB that `Paste` also pastes formatting, number formats, etc. So obviously if you need to preserve *all* properties of the range, `Paste` is the preferred solution IMO. If you need values only, then `Range.Value = array_of_values` is ideal. If you need some combination of values & formats, then I would combine the direct value assignment *and* `PasteSpecial` (the latter for formats, etc.) — David Zemens, Aug 13 '18 at 16:56
This is basically asking for opinions, as that is what you will get. I like A because of Y and dislike B because of Z — Scott Craner, Aug 13 '18 at 16:56
@ScottCraner I see what you're aiming at and also why you would interpret it in that way, but I disagree. I did explcitly try to point out measurable facts about each method and I'm hoping for the discussion to be about **a) what is more efficient** and **b) less error prone** I do understand there is a merit of personal preference to it, but I feel like there **lacks even any sort of comparison between these methods, be it on StackOverflow or even the internet** which could prove to be useful **and help users (myself included) pick their own personal preference, based on the facts served!** — Samuel Hulla, Aug 13 '18 at 16:59
@DavidZemens "nothing will beat performance of direct value assignment" I thought so too, but then I semi-recently was [proven otherwise](https://stackoverflow.com/a/45020388/1188513); given *very large* data sets, I was surprised to find out that direct assignment was effectively beaten by the clipboard. — Mathieu Guindon, Aug 13 '18 at 17:35

Mathieu Guindon · Accepted Answer · 2018-08-13T17:59:00.417

This isn't about readability or how many characters you're typing. It's about what you need to achieve.

In other words, it's absolutely not subjective or opinion-based, and like pretty much everything else in programming...

#ItDepends.

If you're using Excel and copy-pasting cells around, do you Ctrl+C and Ctrl+V or use paste special?

Depends what you need to do.

If you mean to copy a cell, its value, formats, data validations, borders, etc.; then .Paste [Destination] is your best friend. That's the programmatic equivalent of Ctrl+C/Ctrl+V... Equivalent to PasteSpecial/All, which is overkill since .Paste [Destination] already does everything you need it to do in this case.

Cell references are a complete no go!

Absolutely. Hard-coded cell references are bad. .Paste [Destination] doesn't force you to do that anyway, so the point is moot.

If the range overlaps it will display a prompt, effectively stopping macro dead in its tracks

Copy and paste ranges cannot overlap, period. You'll get that prompt through .PasteSpecial too.
If you mean to copy a cell's Value, but not its formats, data validations, borders, etc.; then .PasteSpecial is definitely a better idea, since that's the programmatic equivalent of going paste special / values - except it's probably more efficient to just assign the cell's Value with what you want (no need to round-trip to/from the clipboard); OTOH if you do mean to paste formats, or data validations, or whatnot, then this is probably the easiest way.

Paste doesn't "mess up" formats. It does exactly what it means to do. Paste and PasteSpecial are not equivalents. Use the right tool for the job. They are literally the programmatic equivalents of "paste" and "paste special", respectively - if you're in Excel and systematically going "paste special", you'll get your stuff done. But every time you do that to "paste all", you're working harder than you need to be.

PasteSpecial looks like a nice hammer, but not everything is a nail. When you can avoid a clipboard write, it's generally a good idea to avoid it... but then again, if you're dealing with huge data sets (think 100K+ cells), it's possible that it performs better than just assigning the values.

That said:

@ScottCraner I did think about it, but that's not really copy-pasting, but more of a typical pointer referencing, hence I decided not to include it in my question. I didn't want to open a book to the "what counts as copy-pasting" discussion.

That is wrong. .Range(foo).Value = .Range(bar).Value isn't "typical pointer referencing". It's literally taking the values of foo into a 2D variant array, and dumping that 2D variant array onto bar, overwriting the previously held values. As such, it absolutely is a completely valid alternative to hitting the clipboard - but you'll need to test and compare against Copy+PasteSpecial to see if that's the best (/most efficient) solution for your situation:

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 324.21875ms
Setting cell values, single operation:    1496.09375ms


Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        1773.4375ms
Setting cell values, iterative:           105.46875ms

to me, the fact that `It Depends` is the answer, indicates the question is not right for this forum. You are correct that classifying it as an opinion is probably wrong, but it is then too broad a subject for this forum. Again, this is my opinion. But I like the answer. — Scott Craner, Aug 13 '18 at 17:37
Exactly what I was looking for in an answer. Also Im glad you share the same reasoning when it comes to question being opiniom based - although I should have probably done better job explaining in the original question. Wish I could upvote more than once here! — Samuel Hulla, Aug 13 '18 at 17:54

What is the "correct" way to copy-paste data in VBA?

pros:

cons:

pros:

cons:

1 Answers1

#ItDepends.

Linked