0

I wrote a macro copying columns to another worksheet. I used code like this:

ws2.Columns("A:U").Value = ws1.Columns("A:U").Value

It was slow so I tried copying and pasting values:

ws1.Activate
ws1.Columns("A:U").Copy  
ws2.Activate
ws2.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The second one worked much faster. I thought that first method should be better, why is it the other way around?

Community
  • 1
  • 1
Jacek Potapczuk
  • 29
  • 4
  • Not the answer to your question but you should read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 30 '18 at 11:07
  • You didn't test this with the same range. If the different ranges have different data and depend on different formulas this might be different in speed. So test `ws1.Columns("A:U").Copy: ws2.Columns("A:U").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False` against your `.Value` method. Otherwise you're comparing *"apples"* to *"pears"*. – Pᴇʜ Nov 30 '18 at 11:12
  • my bad range is the same for both. – Jacek Potapczuk Nov 30 '18 at 11:24
  • Do you really need to copy x million cells? – SJR Nov 30 '18 at 11:25

1 Answers1

1

The difference is due to the way Excel works: when you copy, all the selected information goes into the clipboard quickly (it uses the RAM memory). The Paste is also (quasi) instantaneous.

The first approach works by looping thru all the cells/columns and transfering the info cell by cell. You can actually see the process on slower PCs.

Rocoders
  • 46
  • 4