 Hello everyone and welcome back to another video. In this video we're going to be using some of the knowledge we've learned in the previous videos and really sort of building on some actual real world beneficial macros or scripts shall we say. So we're now actually going to be using something that you'll probably find great benefit using in your day to day role. And that actual feature is going to be copying data from one workbook into another. And I guess being more specific obviously we're going to be writing this script into one of our workbooks and we're going to be obviously from that active or open workbook we're going to be opening a second workbook copying out some desired information and then pasting it into our sort of our open workbook. We're going to cover off a couple of different ways of doing this from the very basic of just literally copying the data and pasting it in. And also at the end of the video or towards the end of the video we'll be looking at how we can obviously make this a bit more technical and be able to say okay we want to copy some dynamic data so we need to go through that data and find out how many rows are within that data and then also in our file that we want to pull that data into being able to put that data into the next available row. So obviously that detail is going to be very beneficial if you've got data that's going to continue to grow and you want to add to the bottom of it. So obviously make sure you watch all the way through the video to pick up that more valuable information at the end there. So the first thing we need to just cover off we have looked at the previous video but I've just got a file or folder that I've created and in there I've put some sales data. So if we just open up the sales data file you can just see what's in there and it's literally just 10 well 11 rows in total but 10 actual rows of data and it's just some dummy sales data that I've put together. You'll see that I've color coordinated the countries that has real no bearing on the data at all it's just going to help me explain some features of the copy and paste as we go through this tutorial. So what we'll be doing and again while I've got this data on the screen is obviously will be automatically opening this file as I've just done manually copying out our desired data excluding the headers and then pasting it into our source file so this sales report we have here on the right. So we'll close the sales data file and we'll jump straight into it. So the first thing we'll do is the most basic of options and that's literally just doing a basic copy of a defined range that we'll put in and obviously to a defined area. So for that we're going to do a sub routine and we'll call this one basic copy and score paste and obviously I'll mention this again later in the video but the sort of code that I'm using here I will link to a file where you can download that Excel file to be more specific so if you're having any problems when you're typing this in you can obviously go and pick up that actual code that we're looking at here today. So the first thing we'll do is open our workbook. So for us it's going to be workbooks.open and we just now need to enter in here the file path for that workbook. So the easiest way to do this is obviously to find your file or the folder that your file sits within and for me you can see it's just sitting here so I'm just going to go into the navigation pane at the top here and you can see we've now got the full path for that file. Once it's all done I'm just going to copy that out and then paste it into these two quotations and then the last thing I need to do is if I just move my file over the side here so I can see is I just need to do another backslash and then type in here sales data and making sure I get the correct file extension excel sx so that we've got the complete path to this file. So this tells our VBA code or this tells our Excel that we need to open this workbook and this is the file path for that workbook to open. So once we've now done that the next part is fairly straightforward. We just now need to go workbooks and this is where we're now referencing the actual files we've got available to or the sheets we've got available in that workbook which is now opened and we want to go open sales data.xlsx so it knows what workbook we're referring to just one which now opened and we want to then go into sheets and we're going into the sales sheet so we're in that file you may have noticed that the sheet that the data was in was just the the sheet named sales. Now we can just go into dot range and let's do open here so we can now define the exact range we want to copy so for us it's going to just be quotations a to to g11 so this is the specific range that we want to copy copy and then we're going to do an underscore here and go underneath that code so what the underscore means is just a continuation of our code so we could write this just to the right-hand side but I'm just going to actually rather than complicate it what I'm going to do is I've got my copy there and all I now need to do after this space is the location in which I wish to paste that information so for us we can now use a new variable called this workbook so what this allows us to do is rather than refer to this file we currently have open of sales report we can simply put this workbook and what will happen in the script is we'll say okay the code we're now typing is contained within a certain workbook therefore that workbook is known as this workbook so nice and simple one there and a new little one we can use we're then going to do sheets open total sales I'll keep saying open but it's not actually open there's just obviously refer to total sales and then we want to just go dot range and go that a to enter and you can see it's formatted for us and the better way to do this is it was type that all the better way to do this but when we work with copy range dot copy once you do obviously a copy arrange the next part of the next very we need to enter is the destination so after the space you don't need the brackets around it but after hitting copy into space you just need to put them the destination path we want that data to go and that's exactly what we've done here a bit more detail so this first part here is obviously the range copy that you have below and then this next part here is the destination in which you want to paste that data once we've obviously pasted that data the last thing we would like to do is just now save or save and close this workbook that we just opened here called sales data so all we need to do then is go workbooks and refer to this workbook in particular so we've got sales data dot Excel sx so it's really crucial must always remember to get that correct file extension when you're referring to it dot close and another simple option we've able there and then lastly we're going to do another parameter we've got here is just going to do save changes no changes are going to be getting made to the file because all we're doing is copying from it but we just set that to true so that it just does a save when it closes so this is an entirety of the code we need and I could just expand this here just so you could see it ever so more so yeah all we're doing is opening the workbook we're then saying this is the static range you want to copy and this is the static range we want to paste to and obviously sales data close and save changes once we're finished so let's just reduce this window down ever so slightly so now if we run this code hitting f5 you wouldn't have seen that because it happened on the other screen but what happened is the file opened up on my screen it then obviously took this static range that we had here and it's pasted it into our excel file and obviously alternative afterwards is then just close that workbook as well so that's a tick and that is our basic copy and paste method completed so the next option when it comes to copy and paste is doing pretty much the same as we've just done there but this time actually pasting values so the benefit of doing this is as you saw in the last example where we copied across all different the color formatting of each row this time we just want to copy across the rule value so we don't want any of the cell formatting so to do that it's very much the same and just going to cheat here and I was going to copy the very this code we've got here first so let's go copy and this subroutine will call sub paste values just so we're not referring to and I can just paste that copied code into here and we are going to just remove this first or this middle line like so because obviously the first line of opening the workbook and the closing of the workbook is going to remain exactly the same it's only this bit in the middle that we need to change so the first part we're going to do and actually this part is going to be very much the same as well but we'll just type our game again just helps obviously repeating something helps make it become more familiar so we need to go workbooks and refer to our sales data dot xlsx and we want to go to sheets data well it's about with a d data dot range and we're going to go to a to and g11 that once again and once we've got that we need to do copy and in this time we can just go on to a new row and let's because our code is getting a bit bigger let's start commenting what we're doing here and in our previous videos you might have seen us do this if you're not familiar it's really simple to add comments to our code all we need to do is hit the uh i can't think what's cool but it's on the same key as the app button you just don't need to press shift and that allows you to do this uh this icon here this comment out and we're able to then obviously it'll put our text to green and this text we put in green won't be executed on our code so it'll be there as a comment you can see on the screen but as far as the script is concerned when it's running it won't it won't cause any problems so the first part here is open source workbook and you'll see it turn green as i click away now the second part here is we're going to do uh okay so we are um selecting or copying should i say copying our source uh sales data and then the third part we need to do now here is okay define define range um define destination and paste values okay so for this one again it's going to look very similar to that last one we're going to do workbooks or no we're not going to do workbooks we're going to do this workbook as we know it's a lot easier or not easier but it's a simpler way to refer to where we want this particular workbook we're working with this workbook sheets and it's called total sales and we want to go into dot range oh my spelling is gone all by yeah being navigating the keyboard has gone a bit all over place and we want to go to a2 again and this time we want to go dot paste special and again previous video you've probably seen us use paste special so this works exactly the same it's just obviously we're using them a bit more technical code should we say and paste special the last part i need to then define what that paste special is so you paste colon equals excel paste values and when we hit enter you can see that it's reformatted my text just so we know that it's actually accepted the information i've provided and this last bit as you know is going to be a close and so source workbook okay so this time we can now run this code and we'll see what happens i'm just going to hit f5 and there you go you can see that it's now obviously pasted our data into this obviously into the destination file the difference obviously we're using the paste values as as you can see none of the formatting in the cells have been copied over so we haven't got any of the obviously highlighted cells here i mean yeah highlight cells or rows should be more specific and you can even see with the formatting of the date and the sales total obviously that hasn't pulled across that formatting either so like i said working with paste values has its uses as does obviously copying or formatting as well but this is just to show you another way i've been able to do this using the paste value method it should use so required this last option that we're going to look at is the more complex and it's probably going to be the one that you will want to use the most especially if you're working with recurring reports where you might want to be pulling let me say monthly data into our total sales file over here and you want it to gradually build each month so obviously with each month you want this list to gradually grow rather than just being replaced as we've looked in this previous two examples because obviously if you run that code multiple times it would keep overwriting the same data that it already pulled whereas this option is going to allow you to find the next available row and add onto that data so we'll jump straight into the code there's going to be a few more bits that we need to define and obviously reference as we go through this so i'll try and go through it um obviously as slowly as i can and obviously referencing each part as we go but just to obviously remind you that you go into the links of this description or description for this video below you will find a link from which you can download this excel file i'm using here so obviously it'll give you everything you need to just copy the code uh if you do get stuck along the way so what we're going to do is get jump into it so we go sub and we'll call this copy sales underscore data and in we go so the first thing we need to do is we're going to start defining these worksheets rather than just writing the code each time so the first thing we need to do is go dim and we'll say dim worksheet copy so just ws being short for worksheet and we'll define this as a worksheet so this is the file that we're going to copy from uh we'll do destination now so we'll call this one ws desk as in worksheet destination and that's also as a worksheet a worksheet uh the next thing we need to do is we now need to find a couple of integers so these are going to help us to define the row it's so in this source file if i brought it across you can see it so we know that there's 11 rows of data in our source file so what we want this uh first integer integer to do is to say right how many rows of data is there so okay we can go down and say right there's 11 or if this grew and we had an initial one or two pieces of information we wanted to learn something say okay well we've now got 13 pieces of information so it will continually update for us and on the contrary obviously if there's less data it would again obviously it would identify that there was a lesser number of rows so to do that we first need to define these as integers so we're going to go dim and i'll call this one copy last row as integer and the last one we need to do again is for the destination so this time we want to go into our destination file and we want to identify what is the next available row so for us i mean as we look at the moment we can see there's information in row one which is the headers so we'd want our data to be pasted into row two if however there was some information down into row six and i've just copied country there just gives me a basic example we at this time want to paste into row seven because obviously there's already some existing data here we don't want to replace we want to go into row seven to obviously start collating that more information in addition to what we already have so what we need to do here is go dim and we'll call this one dest last row you can obviously call these whatever you want the integer seems the most logical as we're typing this out today and let's start commenting as well with what we're doing to help reference where we are in the code so the first thing we need to do is we need to open the workbook to copy from and in order to do that it's going to be exactly the same as what we've already looked at so it's going to be workbooks.open and in our quotations we're going to put our file path which i've just copied over to the side here and again i can't stress we need the importance of making sure you put the correct file extension here as well and also one more thing to stress because obviously the code if you source this file will be different if you just need to make sure that you go into wherever the file is stored and you literally go into this navigational here and you literally just copy this exactly as it is obviously adding the additional backslash with the file reference at the end there but obviously yours will be different to what mine is so just make sure you make that update if you do copy and use this file so i've got my sales data reference here the next thing i then need to do after that is it's going to be to is define each workbook and by that i just mean we're going to set to give each workbook a variable to use rather than have to keep typing this out so to do that we're going to do set ws copy equals to workbooks and it's going to be obviously sales data dot xlss xx sorry dot sheets and we want to pull from the sales data sheet within that workbook and you can see obviously it's done the formatting for us that's obviously always a great tick as i keep stressing to let you know that what your input is correct and the second one we want to do is set ws best equals this workbook and it's going to be dot sheets and it's going to be total sales and you can see there's an error there because i did a zero rather than a closed brackets so number three this time we now need to define last row in source data so we know how many rows or rows you want to source from this file so this we need to take this variable from the top here copy last row any copy and paste it there so this is going to equal and it's going to be ws copy because now we've set this variable in this row above we don't need to type all this information out we literally just need this variable ws copy ws copy dot sales and we want to go to ws copy because we now need to refer back to that again to do our rows dot count so they will perform a count of how many rows are in that sheet once we've done that we didn't need to just say okay we wanted to do that in row or column a sorry dot end excel up so once it goes to the end of that range or finds that last row in there we want it to go up round row rather than going down right or left and lastly we're just going to then confirm that we wanted to do it on the rows if that doesn't make too much sense to just there there is a dedicated video i've done on the channel so you can check that out in our vba playlist what is also linked below this video should you want to recap or to look at that in more detail once we've done that the next thing we need to do is define the next or the row that we wish to paste this copy information to that is located in our you know our destination file so for that we're going to do number four or the four thing we need to do here is we're going to go define the next empty row in destination sheet so for us again just to let you know that would be in this example here would be row number two because that's the next empty row that we have so for this we're going to go test last rows so destination last row equals it's going to be wsdest because again we don't need to do that again doing dot sales open brackets and then we've got wsdest again so we can we can do the count of the rows dot rows dot rows dot count and again this is going to be in column in column a so the first column we have dot end excel dot row so exactly the same as what we've done there but the problem not the problem but the the point to mention here is at the moment with this piece of or this line of code it's going to tell us row number one because it's going to what it basically does is it rather than it says what is the next empty row in here it's literally just saying go to the last row that has information in it and for this example in in our destination file obviously that's going to be row number one when it comes to our source data that's great we just want to go to the last row what's being obviously used in that file because that's as much data as we wish to pull but when it comes to our destination we don't want to pace into that last row we actually want to go to the next one so we just need to do a little addition here of plus one so what happens is because we define this as an integer this is going to return a number so obviously we'll return the number one and by adding that plus one obviously it's going to turn that increment that result into an additional one value so it will return the value of two so it allows to paste into this row so again hopefully i've not made that more complicated than it needs to be um for the purpose of this tutorial you don't need to know much more detail about how this works other than this is the piece of code that you need to use to be able to perform this copy and paste so last thing we need to do now or not the last thing but the next thing we need to do is go five so we now want to obviously copy that information across so we're going to go copy range and we're going to yeah so it's going to copy the range that we've selected here from our source file and it's going to go paste to destination so this will probably look like a familiar piece of code too as well so we need to go ws copy dot range and this is where we define the range we wish to copy so we know the first part is static so it's always going to start in column a and it's going to start on row two because we don't want to copy the head is we want to copy the first piece of actual data from that range and we want to go over to column g but that is as far as we go with static information the next thing we need to do is go and copy last row so what this does is this copy last row we know that a variable is going to get picked up in this um in this line of code here so it's going to find what is the last row that has data in it and it allows us to reference it here so we know it's row 11 so it's in essence going to format this all changes to number 11 so that we have a range of a2 to g11 if obviously we had more data and we were up to row 15 it would automatically update this to us for a2 to g15 so we don't need to make any manual adjustments to that all we then need to do is copy that range to dot copy hit space and you can see it now asks us to provide the destination so very simply it's now wstest dot range and we want to do it into column a and once again we're going to do a little and symbol and this time we're going to enter desk last row so it's now going to provide us what is this next available row available in which we want to paste to close brackets and that is the copy and paste now performed the very last one here is we're going to literally just now again do a close source file and save oh well the better way to word that is close and save source file so that we know what we're referring to here simply workbooks dot oh no sorry workbooks open brackets sales data dot excel sx close quotations dot close and in the last part here we've got the saves changes so we're going to put save changes and codon equals true hit enter and that is now all of our code so what's going to happen here is it's going to open our source workbook it's then going to set our variables so it'll define this variable of ws code as that workbook we've just opened and it's also going to do the same for this current work but we want to paste to the third part i was going to do is it will define what is the last row within our source file that has data in it so we know how many rows we wish to copy our destination last row is then going to define to us in our destination sheet what we have on the right here what how many rows we are using go to the next row so that we know we're going to be pasting always into the next empty row the last one the last part keep going the last part but point number five is then literally going to perform that copy for us this did not it's going to look at this dynamic range here based on how many rows we have in our source file and copy that range and it's then going to paste that copied range into again another dynamic row based on how many rows we have of information in this sheet on the right and then lastly i always keep like saying lastly it's then going to close that sales data file what is our source file and make sure any changes are saved and as we touched on earlier that aren't the changes that are going to be made to the file because all we're doing is copying the data out of it so at least this way i just make sure the workbook does to save any changes that have map made and that is the extent of the code so all we can do now is just run this code so i want to select within the sub and hit f5 and you can see it's now copied and pasted all that data across and we've done a simple copy and paste so you can see all the formatting has come across as well if you don't want this obviously with all the formatting that we have here obviously you can refer back to this second one we looked at the paste values and ensure that you're doing your paste as this with the paste special so that's an easy change to make should you wish to do so and as we can obviously see that everything has come across as desired the real benefit of doing this now is if i was to continuously run this book to hit f5 again you can see how it finds that next available row what was row 12 and now continually as the data or appends that data to the bottom of the existing data if i did it one last time so clicking on the sub and hitting f5 you can see the new data is once again added to the bottom so this is really really beneficial like i say if you have a scenario where you're wanting to source new information that is in that file and always add it to the bottom of this file so in future videos we'll look at some more it'll build upon this example to look at more technical examples such as sourcing from different files uh well to name just the one that comes to mind so we'll look at more stuff like this but hopefully this now gives you all some building blocks upon the previous videos we looked at to use some really beneficial code that you can start using and transforming the way that you use excel obviously excel being what it is we're always copying data from other files or trying to merge files into one and like i say this is a real beneficial way of using excel vba to automate that potentially timely process so this video was a bit longer than normal i hope you still managed to stay or stay interested i guess is the keyword here and make it to the end if you have please do give the video a like as it would be greatly appreciated by me as it does obviously take quite a bit of time to pull these bigger videos together and plan them out as well but obviously if you give it a like it would obviously also really help the youtube algorithm to make sure that more people can find this video and learn the techniques that were basically covered in it if you the first time you found the channel or you have watched previous videos of ours before and you've not yet subscribed to the channel please do consider subscribing to the channel and hitting that bell notification button it's also i'm gonna say it'd be really appreciated by me as it is very beneficial to the channel as well and obviously by hitting that bell notification button you'll be notified of all of our future videos as they come out so last things any questions do drop me a comment below this video and i'll get back to you as soon as i can and you've also got the links to this file that we've been using here in the description to this video as well so you can go download that for yourself if you're having any troubles or you just want to obviously use it to follow along at home thank you very much for watching and we'll see you in the next video