How much time do you spend copying data from one sheet to another in Excel? It’s annoying, time-consuming … and prone to human error. It leaves you thinking: “there must be a better way …”
The good news: copying data in Excel from one sheet to another is what VBA absolutely loves! Even beginners can harness VBA to start moving data round.
In this video, I show you how to do this task at 5 levels of sophistication: from a simple one-line macro that an Excel beginner can use, to the actual mechanism I use on my projects to copy data from one sheet to another with Excel VBA. Are you ready to automate that time-consuming task? Let’s do this!
💻Download File Link
tinyurl.com/59sudyub
💼Tiger Excel Basics - Rebuild Your Excel Foundation
www.tigerspreadsheetsolutions...
1. Basic A = B Operation
Let’s start with a basic A=B operation to get data moving around Excel using Excel VBA. The trick here is to remember which of A or B is the ‘destination’ (where the data ends up), and which is the ‘origin’ (where the data starts).
A (Destination) = B (Origin)
In this beginner Excel VBA construct, A is the destination and B the origin.
To translate this into Excel VBA:
Range(“B1”).value = Range(“A1”).value
copies the data from cell A1 into cell B1.
Next, try ‘stacking up’ multiple lines of code to perform multiple data transfer operations. All lines of code are executed as part of the same macro. Cool!
2. Basic A = B Operation Across Sheets
A small additional piece of code allows us to copy data between sheets. It’s as easy as:
Sheets(“Template”).Range(“D1”).value = _
Sheets(“Data”).Range(“A1”).value
Once again, try creating multiple lines of code to make the VBA routine even more useful. Note there is quite a lot of code accumulating in the VBA editor, now. Hhhmm …
Are you ready for a big step up in terms of difficulty … and power? I am going to introduce you to the actual mechanism I use to copy data from one sheet to another with Excel VBA. Let’s do this …
3. It would be perfect if we could control the data transfer … from a worksheet. That would avoid going into the VBA editor all the time and would make it easy to tweak or add to the data transfer.
We can do this with a table in the spreadsheet and a few supremely powerful lines of Excel VBA code.
Go ahead and create the 2-column table, or adapt the example in the download file.
Next: the supremely powerful line of Excel VBA code.
Warning: it’s a bit mind-bending! But the flexibility it creates is just awesome …
Range(Range(“C4”).Value).value = Range(Range(“C5”).value).value
Woah! What happened there?!
By embedding another cell reference within the usual Range(cell_address).value construct, we get Excel to read a cell input as a cell address. In other words, we control the origin and destination cells with cell inputs. Oh, yes! Punch the air …
By adding the sheet references, we can instantly copy data from one sheet to another using Excel VBA. And the process is controlled with worksheet cell inputs. Yes!
Sheets(“Template”).Range(Range(“G4”).Value).value = _
Sheets(“Data”).Range(Range(“H4”).Value).value
💼Tiger Excel Basics - Rebuild Your Excel Foundation
www.tigerspreadsheetsolutions...
4. Loop through the table
A loop allows us to do this with minimal additional code. Let’s use a ‘For Each’ loop to tell Excel to do something to each cell in the data transfer table, and use the .offset method to reference the cell to the right of the column we are looping through.
Dim Chris_Cell As Range
For Each Chris_Cell In Range(“G5:G7”)
Sheets(“Template”).Range(Chris_Cell.value).value = _
Sheets(“Data”).Range(Chris_Cell.offset(0,1).value).value
Next Chris_Cell
I’m counting 1,2,3 .. 4 lines of code. Yes, just four lines of code to transfer data from one sheet to another with Excel VBA. Why did I not try VBA earlier? I know!
5. Make the code dynamic (new entries)
Now, let’s take this solution to professional level. Things change in business, so an Excel VBA application should have the flexibility to respond to changes. Formulae and VBA should not be ‘hard-coded’; rather, we should aim for a ‘dynamic’ approach that can be quickly tweaked WITHOUT having to edit formulae or access the VBA editor.
It is possible with some advanced VBA. Hold tight!
Dim Chris_Cell As Range
For Each Chris_Cell In Range(Range(“G5”),Range(“G5”).end(xldown))
Sheets(“Template”).Range(Chris_Cell.value).value _
Sheets(“Data”).Range(Chris_Cell.offset(0,1).value).value
Next Chris_Cell
The result? A powerful, flexible, professional-level Excel VBA mechanism to copy data from one sheet to another in Excel.
💼Tiger Excel Basics - Rebuild Your Excel Foundation
www.tigerspreadsheetsolutions...