Improving VBA Efficiency when Copying

[Joe4]

Calling all VBA efficiency experts! Here is a multi-part question:

I have read that in speeding up Excel macros, avoid the use of “Copy” and “Paste” whenever possible.
For example, use:
Sheet2.Range(“B1:B200”).Value = Sheet1.Range(“A1:A100”).Value
Instead of:
Sheet1.Range(“A1:A100”).Copy Destination:= Sheet2.Range(“B1”)

My first question has two parts:
1. Can you do something similar when copying a whole sheet of unknown size to another sheet? If so, how?
2. Can you do something similar when just copying cell FORMATTING, not values? If so, how?

My second question is this:
I also read that putting “Application.EnableEvents=False” at the beginning of your code may speed it up. What does this do? What are “events”?

Thanks.

 

[BobUmlas]

1 - If you mean usedrange (all sheets' sizes are the same: 65536x256!) then you can use usedrange.rows.count and usedrange.columns.count and then instead of Range("A1:A100")..., use Range("A1).resize(usedrange.rows.count,usedrange.columns.count)

If you have no code for events, then turning them off won't have any impact on thr running of your code. If you DO have them, then you know what they are!! For your info, however, they are things that nappen, like selecting a cell, changing to another worksheet, 3:45:07 "occurs", opening a rorkbook, calculating, changing a cell, etc. Code can be written to run before the event hapopens. Also BeforePrint, BeforeSave, beforeRightClick, and more...

 

[Joe4]

Bob,

I think you misunderstood my first question. Let me try again:

Using the code

Sheet2.Range(“B1:B200”).Value = Sheet1.Range(“A1:A100”).Value

Instead of

Sheet1.Range(“A1:A100”).Copy Destination:= Sheet2.Range(“B1”)

is more efficient and runs faster because you are not using the "Copy" and "Paste" commands.

That works great for copying a known range. However, let's says I want to copy a whole worksheet to another worksheet. I could do it with the following code:

Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet2").Range("A1")

However, that uses the "Copy" command. I am looking for how to copy the worksheet without using the Copy command, like was done above in the example with the defined range.

 

[Legacy 12630]

The 2 bits of code you posted do not do the same thing.

Copies/pastes values only:-
Sheet2.Range(“B1:B200”).Value = Sheet1.Range(“A1:A100”).Value


Copies/pastes everything (including formulas and formatting).
Sheet1.Range(“A1:A100”).Copy Destination:= Sheet2.Range(“B1”)


To paste the whole of a sheet to another sheet, the following code is not really so inefficient :-

Copy all :-
Sheets("Sheet1").Cells.Copy Sheets("Sheet2").Cells

Copy values only :-
Sheets("Sheet1").Cells.Copy
Sheets("Sheet2").Cells.PasteSpecial Paste:=xlValues

If you want to copy/paste values only without using the copy/paste commands, then it would be something like the following (similar to what B. Umlas suggested). It may or may not be more efficient, but any difference is likely to be immaterial :-

Dim rng As Range, ref$
With Sheets("Sheet1")
Set rng = .Range(.[A1], .UsedRange)
End With
ref = rng.Address
Sheets("Sheet2").Range(ref) = rng.Value

 

[NateO]

Hello, I actually timed a few of these here. Please feel free to do likewise on your system, my results were the opposite of the original post's assertion.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2003-02-12 20:16

 

[bobm]

Hi jmiskey,

To address your second question, EnableEvents allows you to turn off normal workbook events during your procedure - it allows you to avoid messy recursions.

Try this:

In Sheet1,

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Cells(1, 1) = Target.Value * 2
End Sub</pre>
In Sheet2

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1) = Target.Value * 2
Application.EnableEvents = True
End Sub</pre>

Enter the value 5 in any cell of both sheets.
Check the results in "A1".

Bob

 

[Joe4]

NateO,

You said you timed them and the found the exact opposite to be true. I found these tips at http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

What gives? Do you think this site is posting some bad advice?

 

[Legacy 12630]

On 2003-02-13 07:42, jmiskey wrote:
NateO,

You said you timed them and the found the exact opposite to be true. I found these tips at http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

What gives? Do you think this site is posting some bad advice?

 

[Joe4]

Ponsonby,

Is that a yes?

 

[NateO]

Hello again,

What gives? Do you think this site is posting some bad advice?

I think it's a great site with a lot to offer, I've learned a thing or two or three from my visits and Dave's posts. And, I subscribe to the newsletter. In general, no, I don't think so. It's a great free resource.

This may be an outlier, I can't substantiate your original assertion no matter how I run the test, e.g., paste values in new workbooks/new modules (compiled/non -compiled), paste return times in columns or msgbox's, it's always twice as fast for me to do the opposite...

'Tis why having a having a timer, especially the high resolution Windows variety (versus Excel's eccentric idea of time), used in the linked thread, can be critical.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2003-02-13 10:19