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:

1. the (Range) .Copy([Destination]) method
2. the (Worksheet) .Paste([Destination, Link]) method
3. 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?

Answer 1

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