Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Improving VBA Efficiency when Copying

This is a discussion on Improving VBA Efficiency when Copying within the Excel Questions forums, part of the Question Forums category; Calling all VBA efficiency experts! Here is a multi-part question: I have read that in speeding up Excel macros, avoid ...

  1. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,873

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    New York
    Posts
    71

    Default

    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...

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,873

    Default

    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.

  4. #4

    Join Date
    Nov 2002
    Posts
    981

    Default

    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


  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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, Nate Oliver

    [ This Message was edited by: NateO on 2003-02-12 20:16 ]

  6. #6
    Board Regular
    Join Date
    Jan 2003
    Posts
    359

    Default

    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,

    Private Sub Worksheet_Change(ByVal Target As Range)
    Cells(1, 1) = Target.Value * 2
    End Sub

    In Sheet2

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


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

    Bob

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,873

    Default

    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?

  8. #8

    Join Date
    Nov 2002
    Posts
    981

    Default

    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?

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,873

    Default

    Ponsonby,

    Is that a yes?

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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, Nate Oliver

    [ This Message was edited by: NateO on 2003-02-13 10:19 ]

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com