Excel VBA to Move Data Around a File – My Approach
Excel VBA to Move Data Around a File – My Approach
A super powerful approach to use Excel VBA to move data around a file
The super powerful Excel VBA technique I use to quickly and flexibly move data between Excel worksheets.
Moving data around an Excel file? It’s a topic we’ve covered before. And it’s worthy of further attention because so many people out there are trying to do it. This kind of manual, repetitive task takes time and can frustrate!
First, I recommend checking out our Excel VBA for Post-Beginners series where we apply a basic approach to move data from a database to a report template.
This approach certainly works and is a great starting point for beginners. But what are the drawbacks? Well, if we wanted to tweak the code (for example, to add another piece of data to the report), we would have to access the VBA Editor to update the macros. This is not ideal – it is unlikely a typical client would be confident amending the process in this way. Is there a better way?
The approach I teach in this video series offers superior speed, flexibility and robustness. It is the approach I actually use on my Excel VBA projects, and it allows both programmer and customer to quickly update the report if required, without going into VBA. Sounds cool, right? Let’s get into it.
Excel VBA to Move Data Around – TUTORIAL VIDEO 1
In the first video, I explain the mechanism, and begin to set up a new mechanism from scratch, for demonstration purposes. Yes, it takes some time to set up (we actually do not do any exciting coding in this video), but the rewards are significant. The first step is to create a table on a supporting sheet in the file; the table displays information about where we want the data to go (destination) and where the data is located initially (origin). Sheet names and cells are required – you can simply type them in.
Yes, I am asking for some viewer patience here! But, this rather routine task creates magic later on. VBA will actually interact with this table to move data round as we require. This means that building up the process is as a simple as adding new lines to the table.
Further, any tweaks required later require just a simple adjustment to the table data. This is why programmers and clients alike should love this approach: after the initial code setup (we cover that in the next video) it facilitates powerful report configuration without having to open the VBA editor.
(If you really cannot wait for the next video, all the code is included in the download file – see if you can work it out for yourself!)
Excel VBA to Move Data Around – TUTORIAL VIDEO 2
In the second video, we begin to set up the short but powerful routine that makes the magic happen. It’s a concise macro with some tricky lines of code; that’s why it’s important to be steady and systematic in your approach, to avoid getting in a coding mess! (We’ve all been there…)
Crucial to the mechanism is a dynamic selection – a selection that expands as new rows of data are added. The first part of the video deals with this ‘dynamic’ code using an established Tiger favourite – Range(range,range). This construct, combined with .end(xlDown), creates the functionality we need.
Next we test the routine we have created. Yes, it’s tempting to simply plough on with the coding, but that might create problems later. We take the time to test the code, using a message box to flash up the cell value; we even add new rows of data to prove the routine is ‘dynamic’. Cool!