 So in this video, we're going to show you how to pass variables from one subroutine to the other to remove any duplication of code that you're having to write and the best way to describe it is with some code I've already written in here So it's very basic and all we're literally doing is we've got a table of employees here in column a to d and you Can see we have another smaller column of table Sorry over here in column g and you can see we've got in the main table We've got this employee ID of a 104 and in our second one we have a 104 We've also got some additional information for that individual So the basics of our code is we've defined that we want to take this information and reformat it and place it into its desired Location in our destination table. So well got some very basic codes and obviously I'm not saying this is the way to do It's you might want to make it a lot more dynamic Obviously rather than a fixed only updating this one ID But please put those thoughts aside and just go with the the principle of this what it's just trying about the show for a basic example So if we're to run this first subroutine, you can see it just takes the name Tim from our second table and places it in the desired spot In name for a 104 here For age it does the same for age and lastly country. It does the same for country So as you can see it's very basic But what we're having to do in each subroutine is Duplicate obviously various numbers of references. So for argument's sake We have to set redefine the sheet name in every subroutine. So you can see it's defined in each one here And also that last one there. We're also having to do the employee ID obviously that's done three times and three times are also identifying the desired row to place that or the desired row in Which the information should be placed. So they don't need to be put in this multiple times Therefore, we have the benefit of passing each of these variables through to other subroutines And you can do this in multiple ways But two particular ways that I do is you could either call the subroutine at the end of each subroutine So it kind of creates a daisy chain effect to go through the subroutines Or what I prefer to do is to create another subroutine altogether So that it actually sort of pulls together all of your individual subroutines So in order to do that, I'm going to just enter here sub and we'll call this run all lack of better phrase and Obviously, that's created a subroutine for us and it's just tidy these up So what we need to do here is we just need to identify obviously what our Continuously used variables are so for us it would be pretty much all of them really So the first thing we're going to do is define that up here. So go dim WS1 as worksheet If I could spell worksheet What was but there at work sheet there we go and then the next one is eyes into so dim I as integer and Dim E ID so employee ID as a string So once we've done that we then need to go set WS1 equals this workbook Dot sheets open brackets, and it's going to be sheet number one Like so we can then obviously do I to state what our employee ideas will we could have done that? Let's just do it above. It's all makes it a bit easier to see employee ID equals a 104 that's a string and then the last part we need to do is okay find where That employee ID is and to do that I'm using match and all match does is obviously it takes this value or this idea of a 104 And it's going to just return the row in which it appears within column a so simple as that and obviously we can then use That row number to obviously to find where we're going to be adding these values to so we're going to do I equals worksheet function Dot match and if you're familiar with using it in In Excel next Excel formula. It's exactly the same It's just obviously put works each up your worksheet function at the front here when we're using VBA And we want to go in WS1 Dot range a Column a in other words comma zero so you get an exact match and then that will return to us our values So just to demonstrate that image message box one often do this just to make sure things are working You can see it's returned to us the number five And if we're to look in column a we can see yet There is a 104 is in row five So that gives us our starting position for everything when it comes to adding the values in here. Let's get rid of that Okay, and then the next part would be now to actually add our variables So for us what we're going to do is call each sub routine on its own so that it can actually place that variable in for us and Again, like I said the sub routines are going to be very small and you probably think why do you not just do this? Or in one, but I'm just trying to defy or trying to show in a very simple manner How we can pass obviously these various different types of variables into all the different some sub routines And obviously you can then obviously expand upon this and I'm sure you'll have more complicated and more detailed ways of wanting to use this so the first thing we'll need to do is We can remove some data from so the first thing we have to do is add name So in order for us to call that sub routine Obviously it's what happened after this code is run all we need to simply put in here is add underscore name and that would call that sub routine so if I was to run this now and Probably remove some data from here You can see all it does is obviously this is really storing all this data But ultimately after it and just calls this sub routine of add name to which it does same adding of the variables at the moment But all it's going to then do is obviously add the name Tim So that is how you can call a sub routine after But we're going further than that. So what we need to do now is we can see all these variables have already been redefined Predefined so we don't need any of this information because it's already stored for us So we can just delete all those like that But what we need to do now is obviously define in here So in these brackets what we've never previously populated before in these videos We need to now define all of those variables so we can bring them in so to put them in order What we're going to have is WS1 as worksheet So that's the first variable we want to pull in the second one. We want to pull in is the I so I As integer and you'll notice when doing it in the brackets. You don't actually need to add the wedding So I as integer and then the last one we've got E ID as string So that's all those added for us And then all we need to do here is where we've got our add name is simply do a space at the end And we now just need to put the corresponding Variables that align to these so simply put if I put WS1 So obviously that is the worksheet and you can see as I'm typing it here It's telling me to add name and it says obviously one is worksheet next is the integer and the next one is string So we just need to make sure that these variables obviously align with what we've coded in that brackets below And all that does is didn't obviously passes our actual stored value onto that next set routine So do WS1 I can then do I for the integer another comma for E ID If I then just cursor off you can see how it's added the space and we know it's formatted for us And then the benefit of that is it means all we need in here is simply just This one single line of code what literally just does the storing of the name and we can obviously reduce that down if you want But yes, roost it down looks a bit tidier And then the next thing we can do is obviously do the same for all of these other subroutines So let's just delete all that information out of there. Let's delete all this information out of here To have that across tab that across just to tidy up and then we'll just do that And I'm simply going to cheat because they're all the same. So all I'm going to do is copy all of this We've got this one here paste into these other subroutines like so I'm then going to literally go down onto here and we'll do add underscore age and then we've got WS1 We've got an integer and then we've got EID and then the last one below we've got add underscore country And we've got WS1 comma i comma EID as well And that is it. So what our routine is simply doing now is this first subroutine is going to do all of the storing of Or all the defining of all of our variables and storing the values for all those variables And then once that's done it just saves us from having to redo this information in each and every subroutine And then all you need to do is call upon each subroutine in turn Of which it will then pull through this predefined data and then obviously you can execute the desired code So if we were to run this now simply all got to do is run this first one of run all You can see it's gone through stored the the variables and then pass that through to this following subroutines as well So obviously you can probably well you will likely want to extend this further So I'm sure you've got many different technical and more detailed subroutines and in all means than I have here But it just goes to show you how you can save yourself from having to type out certain variables multiple times in Various different multiple subroutines So I hope you enjoyed that video if you did Please do make sure you give the video a like as it would be greatly appreciated by myself If you have any questions at all, please do just leave me a comment below this video And I'll get back to you as soon as I can if you haven't already Then please don't forget to subscribe to the channel and hit that bell notification button That way you'll be notified of all of our future videos as they come out and lastly Thank you very much again for watching and we'll see you in the next video