Excel VBA to Copy Data to Sheets
Speed Up Onerous Copy / Pasting in Excel Using VBA
Click here to download the Excel files for this video
BY CHRIS MORTIMER
Have you ever had to copy / paste data in Excel from one sheet to another? Perhaps you’ve had to copy / paste certain rows from a dataset to separate sheets. I am pretty sure you are not the only one who has found themselves frustrated in this situation. It takes SO much time! Surely there is a better way…
We can use Excel VBA to copy data to sheets in Excel. In the video tutorial, we take an example (sent in by a subscriber to the Tiger mailing list) from the world of horse racing. The workbook contains details of horses in races from the past few years; the user wishes to be able to copy the horses from each race onto a separate sheet. Sounds simple enough, right? It’s a typical example and I’m absolutely sure you will have tried to do something similar at some point in your working life – I know I certainly have…
… in fact, I tried to do it for this tutorial (see the beginning of the video!). I copy / pasted a few entries onto separate sheets and was genuinely shocked at how much time (not to mention, energy!) this relatively simple task used up, and I soon gave up! I started thinking about how to exploit Excel VBA to copy data to sheets; the result was an awesome VBA routine that executes in just a couple of seconds. Yes, this manual task that takes up hours of your time can be reduced to just seconds.
In the video, I walk you through the code, step-by-step, using the same systematic coding approach I use in my day-to-day work. Some of the ‘usual suspects’ from the Tiger videos are there – variables, a loop, conditional statements, use of the macro recorder. The code is quite complex but, with some conceptual understanding, you will be able to make sense of it, and hopefully apply it in your work. If this video is too advanced, work through our ‘Excel VBA for Beginners’ playlist on YouTube first.
Good luck and let us know how you get on.