During macro training today, I used a case example given by one of the trainees as an exercise for the class to practise their new found macro skills. In this exercise, they are required to make use of loops to run through all the 2000 records, picking up the last record for each product and putting them into another worksheet.

The first macro I created, make use of copying and pasting to transfer the valid records nto another worksheet. The macro took about 20 seconds to complete the job. Not happy with the timing, plus using copying and pasting in macro is not easy, I decided to replace the copying and pasting with a read and write statement.

To my surprise, the macro completed the job in less than 1 second. So the lesson is to use read and write instead of copying and pasting in your macro.

The file can be downloaded here. get_latest_price.xlsm

Leave a Reply