Vba code is taking too much time to do job

  • I am using below code to copy paste data in same workbook



    But it taking too much time


  • Re: Vba code is taking too much time to do job


    Judging from the name of your procedure I assume you are copy/pasting formulas. This means that all formulas calculate every time a copy/paste occurs.


    You can speed up your code by putting

    Code
    Application.Calculation = xlCalculationManual


    at the start of your code, and

    Code
    Application.Calculation = xlCalculationAutomatic


    at the end.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Vba code is taking too much time to do job


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Vba code is taking too much time to do job


    I would determine the current setting for Calculation so that it will reset to the same


    Code
    Dim    lCalc as long
       lCalc = Application.Calculation
    
    
    '' your code here"
    
    
    Application.Calculation=   lCalc


    The error above is because the error handler is not spelled the same


  • Re: Vba code is taking too much time to do job


    A simple typo which should be well within the capabilities of anyone who professes to 'program' VBA to resolve.


    Code
    On Error Goto errhndl
  • Re: Vba code is taking too much time to do job


    Don't use a Loop! Use AutoFilter for non blanks then copy the resulting filtered range.


    Attach an example workbook to see if this is feasible.

  • Re: Vba code is taking too much time to do job



    in above formula, first it count the range w4:w53 greater then 0
    it found 3 values


    then it copy a4:r46 and paste them 3 time


    first time it copy range a4:r46 and paste them to a47
    again it copy ragne a4:r46 and paste them to a90
    again it copy range a4:r46 and paste them to a133


    it takes too much time to copy paste


    count 30
    it take 26 seconds


    if possible


    it copy first time a4:r46 and paste them to A47
    and
    second time it copy from A47:r89 and paste them to a90


    and continue
    will this process slow down the copy paste value?

  • Re: Vba code is taking too much time to do job


    You were asked to attach a sample workbook.


    There may well be a faster method, but that depends on the formatting of your sheet and the formulas you are using.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Vba code is taking too much time to do job


    OK
    WILL UPLOAD THE WORKBOOK SOON


    my original workbook is about 1200kb


    i have to create a sample workbook for u


    thanx for reply

  • Re: Vba code is taking too much time to do job


    You can attach a file of 1200KB, the limit has been increased.


    If you do a sample workbook ensure that sheet structure, formatting and formulas remain the same.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Vba code is taking too much time to do job


    Pleased it now works, it had to be the repeated calculating that was causing each copy/paste to be slow.


    Sorry about the typo in my code, did you not read the posts by others after my post that gave you the reason for the error?


    The modification of my code by RoyUK in Post #6 is the one you should use.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Vba code is taking too much time to do job


    no need to say sorry, i understand the situation, there is lot's of for help


    Yes i am using your code post by RoyUK
    .
    thanx again for you hardwork and help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!