In the first ‘workalong’ video in this series, we deal with the tricky topic of working across workbooks.  Yes, because we’re talking about a ‘process’, multiple files are involved, so ability to move between them with Excel VBA is essential.  In the video, we first look at how to reference another file with Excel VBA.  Then, we consider the more difficult task of transferring data between files, specifically the current day’s fixture data, the logical starting point for any weekend process.

Typically, you might think ‘copy / paste’ for this job.  With Excel VBA, however, a ‘direct data transfer’ (simply a = b) is much more efficient, as long as you can handle the coding!  (I mess things up at least once in the video …)  Having felt the power of this basic mechanism, it’s time to move to the next level and add ‘dynamic quality’; in other words, A VBA routine that works for 1 fixture as well as it works for 100.  Get ready for step up in difficult here!

We use multiple powerful VBA techniques including string variables and the awesome .end syntax to get everything working … eventually.  Yes, another debug is required before the routine works smoothly.  But, that’s the nature of Excel development, and debugging is what I call an Excel ‘metaskill’ – it’s a super power that makes everything easier.  So make sure you can do it!

Finally, we create a button in the Summary file to trigger the routine and there we have it – a slick macro to do a manual job.  It’s only a starting point but we’re now ready for the next stage in automating your weekend process …