VBA – Speed tips for my Copy and Paste macro

  • Hello, I wrote a macro to copy and paste data from one sheet to another sheet, however, it takes a good 30 seconds.

    Is there any tips on how I can speed this up?

  • Hi WH,

    It probably depends where the hold-up is occurring, if it is during the copy and paste of the ranges then you could try converting them to arrays and then moving the values which should speed things up as outlined in this video:

    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    If the hold-up is due to the pasting and calculating so many formulas, then do you really need to have the formulas? can you do the calculation and just paste the values or is a user going to be modifying the sheet later? You might be able to use a vba change event for the page to return a value rather than pasting the formulas in the sheet.

    If you post an example of your data I could test the array idea, or you could just comment out the formula paste and use the timer see how long it takes without the formula portion of the code.



Participate now!

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