Excel VBA: Copy Data from one Sheet to Another (Absolute Vs. Relative Macro Recording)

what we're gonna do in this lecture is to create a macro that's gonna copy data from different tabs into a summary tab this video is brought to you by unlock Excel VBA and macros course where you're gonna learn how to simplify your work and how to save time by automating complex and routine tasks we're gonna go from beginner all the way to advanced for more information check out the description of this video or go to XEL plus.com slash courses find out more about the course at the end of this video here the concept of absolute versus relative recording becomes really important now the thing is that it can also become your biggest headache if it's not done properly so I'm going to demonstrate what each of these does on their own so how absolute recording behaves and then how relative recording behaves and then we're going to see how we could use a mix of the two to create dynamic macros so let's go to our summary data tab we already have some data in this tab what we want to do with our macro is to automatically go and fetch data from this new data number one tab so this one and then paste it on the bottom of this data set we wanted then to go to this other the number two tab copy this and paste it on the bottom of this updated data set so the challenge here is to find the next available row now the toggle where you can turn relative referencing on and off is inside the Developer tab and we can see it right here underneath record macro we generally record our macros in absolute mode but there are times that you want to turn it on to turn it on you just have to click on it and then when he gets this dark gray background you know that it's on and then when you click on it again you go back into absolute recording so now let's try this let's first have it off and try to find the next available role and this data set and then we're going to turn it on and then record the same macro and see the differences between the two okay so I'm gonna click here on the bottom and create a new macro the first one is the absolute recording okay so my toggle is off now I'm going to bring up the visual basic editor so we can see record while we're recording so alt f11 now the code that's here is what we worked on in their previous lectures our new macro must be recording right here yeah that's the absolute recording okay so what I'm gonna do is to collapse this and just snap my windows into place so that we can see better okay so first I'm gonna click on the header now I can see it recording says range a 3 dot select now I'm gonna use the keyboard shortcut key control arrow down to jump to the bottom of this data set you know I can see it recording that as well now I'm gonna move one cell down ok now it says range a 9 dots alive so it's fixed the cell reference to a 9 now let's see the difference when we record in relative mode so I'm gonna stop this I'm gonna click where I was before and let's turn on relative referencing and let's record the new macro please so again I'm gonna click here now notice it recorded this movement differently it's used active cell so active cell is where I was on and then it's using the offset property so this is something that we're going to learn in more detail later on but just so that you can understand this code the first argument in offset is the number of rows it should move and the second one is the number of columns alright so it did minus 1 because it moved here and then minus 1 it would 1 up the last argument is actually something that the macro recorder records but it's not something that you need and it can be very confusing because wherever you our it always references to range a one but just take this as meaning it's one cell right if I would change like a1 to a2 it would then highlight two cells okay and it's something that you don't need so you could after you finish recording your macro you could just remove that reference okay so now I'm gonna do the same movement I'm gonna do control arrow down okay at that part of it is identical to the way recorded in absolute mode as well now I'm gonna move one extra cell okay so you did active cell that's where I was on last and then it offset it by one row zero columns and one cell okay which is what I have so this one looks better than the first one so let me just stop the recording from here and let's test which one works better I'm gonna copy just this here so that we get more data now let's go back to where we were I was here and I'm gonna run that first macro so we can run it from macros here but since I'm already in Visual Basic I'm just going to click inside the code and run the first version okay I end up at a nine now I'm going to go back here and then click on the second version and I'm gonna run this I end up on the right cell so this looks like it's the solution to our challenge but what if we are right here and we click on this but do we get we run into a problem why because the first statement if I press debug you can see the problem is from here it's trying to offset this by minus 1 and minus 1 and then it's going to jump down by wherever this is so it's going to give us a problem here it's going to give us a problem if we're not in the right place if I do this it's going to give us a problem as well because it's going to jump to their last cell in Excel here and then you can't offset any more downsit it's not going to do what we want it to do I'm just going to do control up to get back up here so it seems like the best combination of the two would be what that we use the absolute referencing here for the second line of code it doesn't matter if we have switched or not and the third line of code we need to be in relative referencing mode okay so let's now try to record the macro that we want that's going to copy data from these two tabs in here I'm just going to remove this part that we practiced on and let's turn off relative referencing okay so we're going to start with absolute I'm going to click on this button to record a macro and call it copy data okay so first we need to move on to the tab that we want to copy the first one is the number one tab I'm going to click here that's where my data starts so control shift down control shift right and remember these ones it didn't matter if you were in absolute or relative recording but the fact that we want to start from a four that's important right that's something we need to fix so I'm going to copy this control C go to summary data tab now I'm going to click here let me switch this to a single procedure view so that we can see it better and now I'm gonna jump down okay right here I need to switch to relative referencing so I'm gonna turn on this toggle and go one down I'm gonna immediately turn it off because I do tend to forget to turn this one off and I'm gonna paste this okay we're still not done because we want to copy data from the second tab as well so now I'm gonna go back here click on this control shift down control shift right control C go back to summary data go back to the top control down Tuggle on right now one down paste toggle off and then I'm going to click on a3 just to come to the top of the data set okay and I can see it recording all these steps here where are the offset ones they are right here and right here let's not forget to turn off that recording it seems like it's working let's test this let me create a button for it as well it belongs to copy data and let's also call it copy data over let's just test this by deleting some of this data set I think this one starts from F so let's just remove all of these I'm going to close the visual basic editor open this one up and click on copy data okay I can see it all coming up here so until 29 let me click on copy data again it should copy them again below this okay so that's how you can use a combination of absolute and relative recording to create dynamic Network [Music] [Music] [Music] [Music] [Music] [Music] you

Channel: Leila Gharani

Views: 392,238

Rating: 4.9172125 out of 5

Keywords: Excel VBA programming, excel vba copy range to another worksheet, how to record a macro, VBA copy data, Excel VBA course, excel macros copy and paste, excel macros move data, excel Vba transfer data, XelplusVis, microsoft visual basic, excel macros insert button, absolute relative macro recording, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel tips and tricks, Excel vba tutorial for beginners, macro button

Id: 8IreWUk1Al4

Length: 12min 19sec (739 seconds)

Published: Thu Mar 22 2018