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:


    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.


    HTH

    Justin