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

there must be a better way to do this have you ever felt like that when trying to copy data from one sheet to another in excel the good news copying data from one sheet to another in excel is something excel vba absolutely loves in this video i'll show you how to do this task at five levels of sophistication from a simple one-line macro that any beginner can use so the actual professional level mechanism i use on my real-world vba projects let's start with a basic a equals b operation to start moving data around excel with excel vba now the trick here is to remember that a is the destination where the data is going and b is the origin where the data comes from let's see how this concept translates into excel vba and it does with this super simple line of code range b1 value equals range a1.value i'm using b1 as the destination a1 as the origin but you can use any cell references you want let's go ahead give it a try right now i'm going to type a value into a1 here whatever you like go into the visual basic editor click into this routine hit the play button at the top and just to keep an eye on cell b1 here gonna play the routine now and we can see there that's our basic data transfer now make sure you take the time to experiment take the time to play so for example let's try a different value here b2 now b2 is the destination value so where is the data going to go i'm keeping my eye on b2 gonna hit the play button and i can see the data in that let's try with some different data let's try luna's name here gonna run the routine again hit play and i can see the data transfer working it's so important for you to play that's the only way to develop the skill now let's try to get this operation working across sheets now this is a little more difficult to understand so make sure you take your time back into the visual basic editor this is the line of code that we're interested in so d1 on the template sheet is now my destination cell and then our origin cell is a1 on the datasheet so what we're expecting to happen in a1 on the datasheet i've got luna in there i'm going to go ahead and play the routine now going to head across to the template sheet and i can see luna's name has appeared in cell d1 once again let's take the time to play i'm going to put my name in now play the routine one more time what are we expecting to happen we're expecting my name to appear just here in cell d1 on the template sheet and that's the simplest way to copy data from one sheet to another all we need to do is to add the sheet name to our basic construct now let's suppose we wanted to move multiple pieces of data how would we do that back into the vba editor all we're going to do is copy paste this line of code so control c on the windows pc control v and now we can go ahead and make some tweaks that's going to allow us to do another data transfer so what do we want to do let's go to the template sheet say i want to get some information into e1 on the template sheet and let's say from b1 on the data sheet so destination first so e1 and then b1 is going to be the origin and then i can go ahead type something out here let's say chris luna in cell b1 click into the vba editor play the routine and then on the template sheet i can see this data being copied through now it's always more complicated working across sheets so make sure you take the time now to practice this one now are you ready for a big step up in terms of difficulty but also in terms of the power of the vba we're using here you see if we have one line of code for each data transfer operation very soon we're going to have a lot of code in the vba editor that's not a particularly lean way to solve this problem so what are the alternatives wouldn't it be perfect if we could control this data transfer from a worksheet rather than having to go into the vb editor every time we want to tweak it or add a new piece of data let's get back into the template sheet here i'm just going to go ahead and clear out this data control shift and down arrow and then the delete key now the data transfer table i just want to clear this out temporarily so control c here and then moving over to the right control v and v for values and enter that means the data is just going to stay there for a while we're going to use that data in a second control shift right control shift down and then delete so back into the vba editor and we're now on the third method now what about this line of code looks a bit crazy doesn't it we've got range g5.value we're familiar with that construct but it is embedded in another similar construct so how does this work let me demonstrate it first before trying to explain the code fully so we've got i've just changed this to templates so destination cell we can see here g5 let's put a cell address i'm going to put g1 in this cell and then in the cell next door which is the origin cell also referenced in the code h5 i'm going to put h1 here so what on earth is going to happen let's see what happens we need something in the origin cell i'm gonna put my name in there can you see how this is gonna work let's go ahead click in the routine and just hit the play button here and i could see chris appearing there again how on earth did that work so let's say destination cell again let's go this time for g three what's going to happen now you might want to pause the video try to work this out gonna go ahead and hit play and i can see chris now appearing in cell g3 what if i change the origin cell to h3 let's put a different value in h3 now and can you see how this is working incredibly powerful construct we've got one cell reference embedded in another cell reference that has the effect of excel reading the contents of the cell as a cell address the next step is to try to use this technique to move data across sheets back into the vba editor now you might have picked up earlier in the demonstration i changed this sheet name what's our origin sheet well the origin sheet the data is going to come from the data sheet so i'm just going to go ahead and type data in here this is our origin sheet back to the datasheet let's clear out these values i'm going to type in something here let's just type in data so we want to get that that value that data from a1 on the datasheet into a cell on the template sheet so the origin cell now what is it well it's a1 because that cell reference that now refers to the datasheet and we put the data in a1 destination cell let's go for g1 here i'm just going to clear out this uh cell 2 so what are we expecting to happen that value data in a1 should appear in g1 on the template sheet so i'm going to go ahead hit play now and we can see data coming in there so we can control this process from the table i'm going to now type in g3 as the destination cell hit play again let's type in h2 as the destination cell hit play again and you've got to make sure you continue this and do your play but we can use this table to control data transfer across sheets using excel vba let's head back into the vba editor and we're now on the fourth macro in the file it is getting complicated now i did warn you i'm gonna go ahead and just play this macro so you can understand what it does click in the macro hit play at the top and you could see instant data transfer using excel vba so what on earth is going on let's go over to the origin cell column we've got c7 d7 and e7 onto the data sheet c7 d7 and e7 so you can see how that data is being bought into these destination cells this is how it's working so onto the technical stuff you can see i've introduced what's called a loop and are you familiar with loops in excel vba super powerful i've introduced an object variable here don't worry about what that means too much but we're looping through g5 to g7 g5 to g7 we're gonna do this data transfer operation in other words we're gonna execute this line of code for g5 g6 and g7 this is how things are working uh g5 contains the destination cell chris cell and then there's this additional construct with integrated head dot offset so dot offset will move away from column tree from column g just across by one column that means it's going to go and reference column h column h contains our origin cell there so let's do it one more time let's go ahead just clear these out into the vba editor selecting in the routine keep your eye on our highlighted range there hit in play and we can see the data coming in so we've managed to copy data from one sheet to another using excel vba all controlled with a worksheet in the file so the final step is to make this dynamic to make this dynamic so what does that mean well it's a nice solution but things change things change don't they in business what if you want to add another piece of information maybe remove a piece of information we want our code to be dynamic so if you do want to add a new piece of information you can do it easily without without going into the vba editor back into the worksheet now to demonstrate this let's copy in all of the data here so all of our data transfer operations apart from the last line and you'll see at the end of the video why we haven't included last night here so control c control alt v v and enter so for each line of hair seven there's going to be seven different data transfer operations back into the vba editor onto our fifth macro here you can see it looks quite similar to the fourth one doesn't it the fourth one was definitely more difficult we integrated a loop uh we used offset to control position but the fifth one looks quite similar can you spot the difference can you spot the difference what about hair on the fourth compared to hair on the fifth range g5 dot and excel down that's the equivalent of clicking in the spreadsheet and doing control and down arrow it's going to go to the end of the data range so what does that mean in practical sex sense excel is going to continue to work through this routine until it gets to the end of the data range so you could add any number of destination cells and origin cells the code would work it's a dynamic solution let's go ahead and see it in action here so clicking the in the routine and then i'm going to clear this out of course by clicking in the routine and hitting play and we can see our data transferred in apart from apart from the last piece of data which is the due dates so i'm going to go control c here control alt v and v just to prove the dynamic quality of this code how easy it is how easy it is to scale up back into the vba editor hitting play expecting a piece of data to appear here in cell e12 hitting play and there the data has come in if you're looking for a structured excel learning program telling you the things you need to know in excel including super powerful vba routines like this why not check out our excel basics course i've laid it out all for you there 27 15 minute videos you can rebuild your excel foundation in less than a month take care guys see you in the next video

Channel: Tiger Spreadsheet Solutions

Views: 84,141

Keywords: Excel VBA to copy data from one sheet to another, Excel VBA to move data between sheets, Excel VBA to transfer data from one sheet to another, Copy data Excel VBA, Excel VBA copy data between any two sheets, Make one cell equal another with Excel VBA, Excel VBA beginner tutorial

Id: Z62yORhPr3Q

Length: 11min 45sec (705 seconds)

Published: Fri Mar 19 2021