Excel VBA: Copy Data Between ANY Two Sheets

hello and welcome in this video I'm going to show you how to copy data from an e worksheet to any other worksheet using Excel VBA and it doesn't matter if the worksheet is in a different workbook or if the workbook is even closed now the beauty of what I'm going to show you is that you can apply the code to any worksheet and it doesn't really change that much so you can take this code and use it in your VBA projects so let's go ahead and get started now if you liked this video please click on the like button below and if you'd like to get notified of my upcoming videos then please click on the subscribe button and another fight bell beside it so let's have a look at a real basic copy operation and we're going to do it manually now what we have here is just some very simple random data now if we want to copy the data we highlight the data and then we copy and paste it somewhere but the first thing we always need to know is where is the range what worksheet it's on and what workbook the worksheet is in so these three things are always important when we're copying our data and when we're pasting our data so for example here I'm going to copy a 1 to B 15 um and that's on the worksheet source and it's in the current workbook which is called copy between worksheets and I want to copy that to d1 so this is on the same source worksheet and not only that it's also in the same workbook so we base to do ctrl C and then we would do ctrl V and we just make it a bit wider there now when we were doing it in VBA we have to do something similar we have to specify the name of the workbook the worksheet and the range that we're going to use but it's very very straightforward to do it so let's go ahead and write the VBA code to do this so the first thing that we want to do is want to delete this data so that we can see that our VBA worked correctly and then we press alt F 11 to enter the visual basic editor and once in the editor we'll do right click to create a module and the module is where our souls go and our subs is where our clothes and subs are also called macro so we're just going to call this one copy data now what we want to do in VBA quite simply is we want to have a source range like this and we want to copy and then we want to have a destination range and we want to do prepare special on that and that's going to be excel paste values so the key here is how we get the source and how we get the destination so we do we're going to declare these of course first of all before we use them and we declare them as ranges so now we've got our source and destination variables and what we want to do is we want to set our range source to equal something like this we want it to equal a workbook and this workbook contains a worksheet and this worksheet contains the range that we want to use so this is always when we're dealing with a range it's always belong to a worksheet and it's always belonged to a workbook so let's do set range source now we know that our range is equal to a 1 so that's our range now our worksheet we know it's in the worksheets collection and it's called source so we use source like this and then we want to specify the workbook that it belongs to and the workbook is the current workbook so we just use this workbook and there we have the three parts of our source now our destination is going to copy to cell d1 so we basically just copied this again and now we'll set our destination our workbook and worksheet is the same so the only thing that we have to change is d1 so let's see how this code works we do a debug compile make sure everything is ok and then let's get rid of these fields just so that we can see it a bit easier here and let's run the code and you can see that a copy perfectly now imagine that we want to copy to this destination worksheet so we want to copy to the destination worksheet so what do we do it's very simple what we do is we just change our destination worksheet here to destination and that's going to copy to range d1 in our destination worksheet so let's press f5 run the code you can see that it went to d1 now imagine we want to do 2 a1 on this worksheet well then it's just a simple matter of changing our range to a1 and then we run the code and you can see it copied now imagine we want to copy from the destination worksheet back to will say the report worksheet just want to copy from destination to report so how do we do it well again is just the same thing so our source this time is called the destination worksheet and our report is the name of our destination so we're just changing it in here so the first line is always our source and the second line is always our destination let's run this code and you can see that it copies the value perfectly so now imagine we want to copy to a worksheet that's in a different workbook than where the code is so how do we go about doing that well this is where it gets interesting so let's have a look at another workbook first of all we've got another workbook here it's just called other file and we just open this workbook and you can see it's got g1 so we've already got the data from when I copied before but let's delete this data so this sheet is now empty we'll save it and we'll close this file so how do we write the code to open a copy to this file well we just need a few lines of code here so we use filename to build the file name so this is just a string of the file name and we set the file name to equal so it's gonna be in the current folder so that's this workbook and that's path so that's the same folder as the current workbook on what we want next is just to build a name we want to use this and a problem with just using this is that on certain on certain operating systems like the Mac or if people use different region settings cause problems so instead of using this what we use is application and we use from path separator and in VBA will basically change to whatever it needs for the current system and then of course we're just going to put in the file name so order file dot Excel so there we have define name just that simple the next thing we want to do is create the workbook so we say dim workbook WK as workbook and we set the workbook equal to workbooks and open so we're opening the workbook and organizing with the file name now at the end when we finish what we'll do is with saved so we'll close the workbook I would say Save Changes equals true so what we do here then our source is going to be the source as it was so we'll just go back to the source as it was on the source worksheet and we want to write out to the order workbook so we basically take workbook here and we replace this workbook with workbook so we've got workbook worksheets and then the worksheet name is sheet 1 an arranged is going to be the same we're gonna write to a 1 so let's run this code and see exactly what happens it's running the cord it's opened a worksheet and it stores that again it's happened very quickly so we didn't see it so we'll go in and have a look and just see what's in order file and you can see now that other file has the data so she one in order file has the data now we wanted to copy the other way around so imagine this time what we're going to do is we'll close this file and we're going to open and we're gonna we're gonna copy from sheet 1 in order file to our report so how do we do that so again it's just a matter of changing what's on these lines so our source is gonna be workbook and it's sheet 1 and the range is gonna be the same and we're gonna write to the report worksheet and that's in the current workbook so we change to workbook now let me just get rid of this space and then we don't we don't in this case we can actually just open it if we like we can open it to be read-only and the reason is that we don't so we don't accidentally overwrite stuff and we can say it read-only is true and this means also if someone has it open we can open it no problem so our source again is the one we're opening and what we're doing is wanna copy it to report so let's just just to make sure that copying exactly the data we expect it to so let's change this one we'll just say a b d EF and we just changed numbers to 9999 just want to be clear that this is exactly the data that are copied so let's bring up our code here like this and let's run it and you can see that it wrote the data as we expected in report and it's a b c d e f 99 9 so you can see how this works so when the setter to start using copy and paste special is good if you have just doing a 1 soft copy with a lot of data but if you want to be doing lots of copying then using something else like assignment is better so let's just see how the code changes if we want to copy in a different manner so again this time we're going to go back to source and we're just going to copy from source so we're going to copy from a1 to d1 so all this and we're going to just put it in D 1 to e 15 so in that case the way we copy by assignment is we normally say destination dot value equals range source dot value and we don't need copy or anything like this now we can just comment out these lines here because we don't need them and so again we know that we're going to be copying from source and we know what that we're going to be copying to source and it's in the current workbook and it's going to be copying d1 now the one difference with the assignment is when we copy we have to resize the destination to match the source otherwise we get we'll get an error or we won't get all the data at depending on what way it goes so it can easily resize like this we just say set the destination equal itself and then we resize it and how you resize it is basic just to the source size so the source Rose duck count and that gives us the size in Rose and the size in columns is basically very similar how many just said columns dot count now let me just stretch this a bit and so now our destination is the correct size and when we assign it here it should be perfect so let's run this code you can see that it worked as we expected so the key in board situations from row copyin using copy/paste or assignment you can see that in both cases it's just a matter of getting the correct range and we get the range always getting the workbook getting the worksheet getting the range so the only thing that ever changes here when the work when we're getting from a different worksheet is once on these two lines so the rest of our code doesn't change because it's going to use whatever destination we give us we give it and whatever source that we give it so this is very important to understand so in this video I showed you how to copy data between any two worksheets and this is even if the worksheet is in a different workbook or if it's in the current workbook now if you like this video please click on the like button and if you like to get notified in my upcoming videos then click on the subscribe button below if you've got any comments or queries you can leave them in the comment section underneath the video and if you want to get the source code for this video then click on the link in the description below and you can download the source code I hope to see you on the next video

Channel: Excel Macro Mastery

Views: 50,541

Rating: 4.9694657 out of 5

Keywords: excel, microsoft excel (software), vba, excel vba, vba copy, vba copy data between worksheets, copy data from one sheet to another, vba basics for beginners

Id: azWysZ6qZWQ

Length: 12min 56sec (776 seconds)

Published: Mon Mar 02 2020