Excel VBA To Copy Data From One Sheet To Another (BEGINNER TO PRO!)

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

💼Tiger Excel Basics - Rebuild Your Excel Foundation

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

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”)
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))
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

[sbatsia]

Further to my recent email, this direct transfer method has been demonstrated and explained in a noticeably clear and comprehensible way. I'm happy to express my thanks and appreciation to you!

[steveknapp]

Advanced Autofilter via vba is by far the fastest way to move data between sheets. I tested copy and paste vs AAF, and the time difference was literally less than half the time. Good, clear content as always 👍 I see you have succumbed to the lock down trim....I’ve had a wife trim very similar 🤣

[ramandeepsingh]

You are superb Brother! I have seen all of your videos and made my notes which helped me a lot with my daily tasks. Thanks!

Lots of Love & Wishes from India!

[gaspumprepairservice]

Good information, thx. I’m learning VBA at 60-something and your presentation is easily digestible.

[ngee]

I really love all of your VBA videos, always super helpful, thank you!

[bartverheyden]

VBA makes this so easy! Really nice topic

[Jojosmith]

Thank you very much Chris for your great lesson. I have been searching for days fortunately I've found you the Super great teacher for VBA Excel. Thanks millions. Subscribed, definately liked! Looking forward to more lessons

[stevennye]

Excellent work, I started off thinking what is he doing? but your code ended up with a much cleaner solution than I expected.

[wrlomprez]

Thanks a lot, I needed this moving a ton of data around this cut time down to nothing damn

[amarbirajdar]

Hey Chris, So what if I have to create a new sheet on daily basis with date as the name of sheet and I have to copy the headers from the previous sheet. I know I have might have to use a variable for the sheet names but I am unable to find some example around it to get a reference. I have created a vba code to create new sheet and change its name to date but unable to proceed further. Can you help me here?

[rudolfnel]

great video i however need to copy over complete columns do you have a video with an example script of that

[martinshaw]

How could you change this to copy a formula instead of just a value? Is it possible

[pradeepprabhui]

Hi there, thanks a lot for this video..
Very informative i must say..
I do a lot of copy pasting from one workbook to another
Condition - the cell that i will be copying from one workbook to another should happen only when a value matches.. (match function)
How can i do this? Need your valuable assistance

[TigerSpreadsheetSolutions]

Which method are you using to copy data from one sheet to another with Excel VBA - 1, 2, 3, 4 ... or 5? Let me know in the comments, I will get back to you ..👇

[hastyscorpion]

I am slightly confused by 6:55. Since you changed the Origin to the Data sheet, why isn't the origin pulling the address from H5 on the Data sheet? Is it because range.value function only pulls from the active sheet?

[NethaLawrence]

This Video is great. One question though how can you have the code work without always clicking the play button? So when you type the value in one cell it auto copies it to another.

[brianbarns]

Hey Chris, This is interesting but what if I want this to work on auto so when data is entered in the 1st sheet it will also appear in the destination sheet with using a play or activate button. The next step would be to sort the data on the destination sheet and only show the top ranking results with the code running on auto. Is there a more detailed video on your website?

[Luigi]

Great video Chris, thank you very much. I have downloaded your file for the link provided and when I run the "Use_Table_To_Copy_Data() routine" and I get a "Run-time error ‘1004’: Application-defined or object-defined error". I haven't changed anything in your code. Are there any settings that I have to perhaps change on my side?

[Ron-elzh]

Love the ideas behind this video. I understand the code, however when I run it from Method 3 onwards I get Run-Time error '1004': Application-defined or object-defined error. The template sheet is active, I added an extra line of code to activate the worksheet. I do not know why I am getting this error?


= _

[arljunelmacasling]

Hi Sir!
Can I ask? where can I put these codes?