Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have had a bit of a shock:

    I use code (see attached) to copy data from one worksheet in a source workbook to a blank worksheet in a destination workbook. Due to the sheer volume of data, this is very slow.

    My thought was: to copy the worksheet (tab) from source to destination workbook, expecting this to be much quicker. Only my own logic led me to beleive that, and I have now found that to be completely wrong, as the process is no faster at all.

    There are no formula's involved, just pure data, but a lot of it.

    My question is, what is the best practice for this, is there another faster approach that I can take.

    [attachment=82706:06.03.09.txt]
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Have you tested whether it's the copying that takes so much time, or the formatting you perform afterwards?

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is only this one line that is extremely slow:

    wkbNew.Worksheets(1).Copy After:=wkbOri.Sheets(wkbOri.Sheets.Count)

    The rest executes in a split second.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this work better?

    wkbNew.Worksheets(1).UsedRange.Copy Destination:=wkbOri.Sheets("Bacs 1").Range("A1")

    Also, if your sheet contains lots of formulas, it would be a good idea to turn off automatic calculation temporarily:

    Application.Calculation = xlCalculationManual
    ' code to copy here
    ...
    Application.Calculation = xlCalculationAutomatic

Posting Permissions

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