 Welcome back to another video. So this video, we're going to focus on how to copy and paste values or data, shall I say, using Excel VBA. So we're going to go over a couple of different methods. And yeah, just try to try and cover off as much as we can in relation to copying and pasting information in Excel. So what we will be doing, all of our previous videos, if you haven't seen any of those, what it means, go check them out. There's a link to the playlist below this video as well. So it's very beneficial to check all those out. But if you haven't seen those videos, you will find out when you watch them that we've been coding all of our VBA script into a module. So this module one here, it's not here by default. You have to go insert module. But for the initial part of this video, we're actually going to be coding within the actual sheet itself. So the benefit of doing it within the sheet is we're able to remove some of the need for some code in terms of referring to the sheet we want to be in. This is just going to serve as beneficial, I guess, as an initial learning curve and also help me to get you started on how to do copy and paste. But we will go into the module bit at the end of this video, just to show you how that all works and how you can bring that together. So obviously make sure you do watch the end of the video so you get that detail as well. So all we need to do to go into our sheet one, or it could be any sheet that your information and you're within, is just double click on the sheet one. And then we're going to put a subroutine in here and we'll call this demo copy and do that. And as you all know by now, because I always hop on about it, is you can call your subroutine anything you like. Obviously best practice is the name. It's something of relevance so that you can easily find it or know what the contents of that subroutine actually are going to do. So the most simple and basic way that we have to copy is by doing this first piece here. So we can literally go range, open brackets. And again, for those who have seen previous videos, you're known I've always defined what sheet we're using first, but because we are within sheet one, it knows that everything is going to be contained within this sheet. So hopefully that does not cause any confusion in this video, but here we go, we're jumping straight in. So the first thing we need to do is to find the range that contains our information we wish to copy. So for us, it's going to be obviously in column A and it starts in row one and goes down to row five. So within quotations, all we need to do is select a one. We're going to then use the colon. So you can use that and do the hold the shift button down and push the code on key. Then the last one we're going to need is a five quotation and closed brackets. So all of our information that we acquire here from row one to row five needs to be captured in this format here in that range. And then very simply, all we need to do to copy it is go full stop copy and that's it. And Excel has now identified that we want to get this range and we want to copy it. This first example looking at is by all means the most simple and easiest way to use because all we now need to do is provide the destination that we wish to paste that information to. So for us, we're going to paste it into column D. But rather than having to now define row one to row five of column D, all we need to do is to find the first cell we wish to start up or start pasting in. And then what it will do is to then just allow the rest of the range to spill over into the desired or the required number of rows. So all we need to do here is go range, open brackets, quotation, D, one, quotation, close brackets, hit enter. And you can see, obviously when you hit enter there, you know the information has been captured correctly because all the correct formatting of adding in capital letters where required has been done for you. If we now hit F5, all the time you can push this play button at the top here. You can see that our information has been copied from this desired range and it's been pasted into D1. And the benefit of using this simple copy and paste method is it copies and paste everything in that range. And by that, I mean it's copied one of the values and it's also copied the formatting what was applied to that first set to copy. So the next one we're gonna look at is say we wanted to copy all that information but we didn't want to paste the formatting. So we literally just wanted the actual bare bones just the values that obviously sit within here. So in order to do that, all we now need to do is what I'll just do is comment out this. So you can see it's now highlighted there so it's not gonna do that copy and paste again. So what we'll do here is this time we'll go range and we'll do the same range. That's going to be a one to a five dot copy. And then on a new line, we can now simply put into here range open brackets and let's put this into G. So G1 dot this time it's gonna obviously be a bit different. So we can now have a range of options. So I've sort of skipped over there. So the first one where we wanted to literally go straight copy and then paste to a range you can see it's all on one row. This time we need to be onto a new row. So we've got the range copy so that's all exactly the same but this time we need to go onto a new row and enter our desired range and where we want to paste our value but this time we're gonna do paste special. You can see what there. And the next thing we need to do is now define what's paste special do you want. So you might be familiar with paste special when using Excel when you can do a right click and you go paste special and you can select some different options such as paste values as we want or paste formatting or any other one and paste formats you can see is available to us here. So if we wanted to literally just paste the formatting we can literally double click this one, hit enter and you can see now if we hit F5 it was gonna just paste nothing but the formatting so you can see all the formatting we've got here even the bold text here and the underline is gonna be pasted into G but we don't actually want the paste formats we actually want to do our paste values. So again, I'll just copy this once more then we can keep all these examples on the screen and we're just gonna copy off, sorry, do that. So obviously those two lines of code won't be run this time. And let's now go to column J so I'm just gonna update it here. And this time what we want is get rid of Excel paste format so we can do a space here and we want to do Excel paste values and you can see what validation there just make sure you have the values and then hit enter and let me just like if you hadn't noticed I always put capital letters when I'm doing the references in these ranges you don't have to do that it's just out of choice and I think sometimes it comes through a bit clearer on the screen as well but if you'd wanted that as well it doesn't matter whatever your preference is. So now we've got Excel paste values so if we run this you can see that we have nothing but just the bare values pasted into our desired range. So that is the three methods you can use so you can either, well it's not the only three methods you can use by no means, obviously you can do various things with paste formatting values and very many other options that you have available but ultimately in this video the first one we have is obviously this simple copy range so this works great if you want to do both the values and retain the formatting and it's the most simple copy paste that we have. The next one you can do is if you want to just paste the formats as you can see in column here column G sorry then all you need to do is got your range copy on the first line on the second line you then need to define what you want to do so for us just paste special and Excel paste formats quite simple and easy to remember and lastly if you want just the values then you can obviously do range copy as we've done here but this time using the space paste special function Excel paste values. One last one to cover off again is to while we're still doing this is another really simple way of doing the actual sort of copy paste is to actually say you want a sales value to equal the same as something else so not quite a copy and paste but obviously another way of doing it. So for this what we're going to just do here is go to range and let's say you can kind of just see column L on the screen so we're going to range L1 dot value equals and let's say range two just so we get a number in there dot. So we're literally just saying that we want range L1 the value in range L1 so this value here we want that to equal the same value in range A2 so that's going to be here so red. So if you now run that as well at five you can see it's equal to that value as well so it's obviously not quite copy and paste but it's another one that just comes to mind and I thought I'd quickly show you. So there are methods that we're going to look at for using copy and paste. Obviously we only did small range we only did up to what five rows here all you need to do to do a bigger range is obviously update the number of rows that you have in this reference or so obviously you can increase or reduce those number of rows depending on what you require. So then I'll see the touch on and let's just remove all this out so we can start afresh. So let's say we wanted to do this from within our module as we've done in previous videos so we'll do sub, example, I don't know so copy, paste, demo, just to call it something. So this time what we want to do so yeah how would we define this calculate or this piece of code if we're doing it from the module so we need to actually define the sheet. So putting it all together in a game from our last video so if you haven't seen that that's going to probably help bring some context of what I'm now going to do. So the first thing we need to do is define what sheet we're using so for us it's thin and we're going to call this SHT1 as worksheet. We need to set obviously SHT1 is equal to sheets and it's going to be sheet one and then we can move down to here. So the first part we want to now say is how to copy that range. So for us it's going to be SHT1.range A1 to A5, close brackets.copy and this needs to be populated to into we'll put into column C so we need to now do SHT1.range and C1, hit enter, run that macro and you can see it's now copied for us. So the main importance when we're now coding this from the module or from a sheet different to obviously the one that's contained in so at the moment if we're going to sheet one obviously the code is in here the values we're trying to copy and paste are within this same sheet that's why we don't need to define the sheet. If we're working in this workbook a different sheet or in this module that's when we need to define what the sheet or the sheet reference else Excel doesn't know where these values are stored that you want to copy and paste. So this is how you exactly do it when you're in this dynamic. And lastly the one we can look at is how to do it as a values because we know the format will be exactly the same. So this time we want to do SHT1.range and again A1 to A5 so dot copy. If we do it and then SHT1.range and let's go to E1 so range E1.paste special and then Excel paste values is down here hit enter, run this code and you can see it's now pasted again into column E but this time with just the values. So this is obviously the more detailed how you need to be defining your ranges when you're working with different sheets and probably what you're gonna be using more often than not and obviously yeah to do your copy and paste. So there are simpler ways that we can do this when it comes to defining sheets and working with particular or working with a particular section of your code in a particular sheet and we will be covering that off in a future video. So to make sure you don't miss that video please do subscribe to the channel if you haven't already and hit that bell notification button so you're notified as soon as our videos come out. What you can also do is we've got another link below this video. So we've got one which gives you the link to the playlist for this VBA series so you can get all those videos together and there's also another link in there as well which gives you the link to all of our other videos if you want to look at other things like VLOOKUP, if statements, pretty much anything and everything else that we're covered in there as well. If you did enjoy this video please don't forget to give the video a like. I'd be not only greatly appreciated by myself but also very beneficial for the all important YouTube algorithm. So again, one last thing as I should say before if you do have any questions or comments please do just drop them below this video and I'll make sure I get back to them as soon as I can. Thank you very much for watching. We'll see you in the next video.