5

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?

Improve this question
15
  • 7
    You forgot the assign value directly: Range().Value = Range().Value Commented Aug 13, 2018 at 16:37
  • 1
    Aren't you ignoring the Worksheet.Paste Method? Commented Aug 13, 2018 at 16:39
  • 4
    "to settle the discussion once and for all" - um... good luck with that! I see nothing wrong with .Copy() if that fits the need. Commented Aug 13, 2018 at 16:48
  • 1
    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. Commented Aug 13, 2018 at 16:55
  • 4
    @DavidZemens "nothing will beat performance of direct value assignment" I thought so too, but then I semi-recently was proven otherwise; given very large data sets, I was surprised to find out that direct assignment was effectively beaten by the clipboard. Commented Aug 13, 2018 at 17:35

1 Answer 1

Reset to default
10

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.