Excel VBA to Move Data Around a File – My Approach

A super powerful approach to use Excel VBA to move data around a file

Click here to download the Excel file for this series.

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!

Excel VBA Move Data Thumb

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!

Excel VBA to Move Data Around – TUTORIAL VIDEO 3

In the third video in the series, we complete the mechanism and ‘play’ with it to understand its unique power. Before we get there, however, there is some intricate coding work to do. With this kind of complex syntax that interacts with objects in the file, the likelihood of errors (and stress!) is high. Therefore, or first step is to implement a simple line of code that ‘does a similar thing’, as an intermediary step. When faced with difficult coding always simplify, test, then build up.

With the simpler line of code in place, we can substitute in the more powerful constructs. In this case we replace normal references for the variable name; using a variable in this way can feel unnatural – but remember that this is a range variable, so it stores a cell address. That means we can use it to reference cells in the file. Cool! Chris incorporates this new element and tests. Finally, we ‘recycle’ a line of code that we know is functioning to complete the syntax; some adjustments to the offset method to get Excel looking at the correct cells completes the process.

What we have created is supremely powerful and flexible. No more editing VBA when we need to add a new item to the report; rather, we can simply add a new line of code to the backend table. Sweet!

Excel VBA to Move Data Around a File – Series Release Dates
Video 1 – 03/05/19 – https://youtu.be/XStFwJW-oCQ
Video 2 – 17/05/19 – https://youtu.be/v2FZcYcOSwA
Video 3 – 31/05/19 – https://youtu.be/156KqQZ-pgA

What did you make of the Excel VBA to Move Data Around video series? Have you used the approach yourself? Leave a comment on the YouTube channel and let’s start a conversation!

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.