Five top tips for improving your VBA speed
1. Write good code: Your script is only going to be as fast as it’s weekest link, create good habits by keeping your code neat, consice and your macro will run fast
2.Screen Updating: Turn off screen updating whilst you work – Added benifit that you wont have a flashing screen as documents are open and closed
3.Automatic Calculations: Turn off these and it’ll stop your formulas recalculating every single time you add to your workbook
4.Enable Events: This is a bit obscure, but it has saved more than one macro from the recycling bin. This is a more complex version of automatic calculations, which might cause you problems, but really improves speed
5. Active Workbooks: Stop activating workbooks, it’s not necessary and will slow your code down. Instead define your workbooks or ranges or what have you and reference the locations.