 Hello everyone and welcome back to another video So we're going to be looking at the VBA also That's going to be a top of the choice for this video and we're going to be using VBA to create a macro in which we can copy and transpose Some data that we have in our worksheet in order to do this will be it'll be a small introduction to using arrays But as you'll see as we step through this There's a bit of a setup we need to do with some of the code in terms of referencing to stuff But the whole process is really simple just to give a visual So you can see on the right hand side of the screen here at the moment We've got this basic table which literally gives us a date going along the top here So this is a growing table So obviously you could get to that the 11th of November and then there'll be some information to store for each city there And you can also see in column a we've then also got the cities And what we want to do even though the data in this scenario is being recorded in this format What we need to do is we need to get all this data and have it transposed like so So we have the format that you now see below So we've got date in this column and then you can see what the cities along the horizontal there So you might want to change the format or sort of say the layout of the data for many different reasons such as if you're trying to put it into a Chart or just maybe different presentation Preferences, but obviously rather than doing this manually We want to do this using VBA and we've also got another video coming up in which we're going to show you How you can also use the same process but have the columns reordered as well And in my experience of using VBA that this is something that comes back to time and time we're getting where we need them to take Like I say a set of data, maybe do the transpose piece as well But ultimately try to rearrange and reformat the cells or columns that we have and therefore we want to have on our output So what we'll just do is get rid of this and we'll step straight into it So the first things we're going to do is we need to make a couple of references So we need to obviously reference this sheet so sheet number one and we then got sheet number two So kind of like the source data and then our output will be presented into sheet number two So if you haven't already you need to open up your VBA developer window and we're going to just insert a module You can see I've already got two modules here as it is But if you haven't got a blank and an empty module ready to use then just go into insert Module and that's how you can get it on the right left-hand side here Once just double-click to open up and then you're ready and good to go So the first thing we're going to do is just make references to our worksheets You don't have to do this It's just good practice and it makes it a lot simpler I find as well when you're reading through the code and understanding what it's trying to do So subroutine is just going to be we'll call it transpose Table And I have on purpose misspelled table there Just a person preference of mine just to make the words a bit shorter there So the first thing we need to do is go dim And we'll call it worksheet one so ws1 as worksheet and then because it's also a worksheet as well We're then going to ws2 as worksheet as well For those not familiar you might you your preference might to then be to dim ws2 as worksheet Below so either Adding a comma and then at the end here or this one here works exactly the same my personal preferences I just tend to group things so anything what's going to be a worksheet I'll try and group together on the same row and the same for like integers or anything else I just I just find personally it helps to give it keep everything together when you're referencing back So the new one we're going to be adding is dim and I'm going to put my array and simply we're going to be storing this as a variant So a variant just being obviously varying format that you can be used I'm by no means an expert on using arrays or obviously the different ways of storing them Obviously just know how to do this and a few other processes with them So by all means if you want to get more detail give it a Google search I'm sure you'll soon find everything you need to know but for the purpose of this video I'll say you'll be covering off everything as much as you need to know it What is in as much detail as you need to understand this process? so the next thing we're going to do is We need to make a couple of references so as you'll see of our data We've got a number of rows and we've also got a number of columns But because what we could do is we could just set a fixed range So we've got literally from cell a1 through to K6 that would work But obviously in an ideal scenario, we don't want to limit ourselves just to this range We want people to still be able to add more cities to this and we want people to continually add dates And when new information is added we then want to make sure that we are picking up that newly added information Each time this is run so to do that We're going to be entering a couple of dynamic references for row and column so it's going to go dim and I'm going to call this last row as Integer and then I'll go last column as Integer as well So obviously we only need to get the last column so column K or the last row column 6 because obviously We're always going to have that same starting point because the data is always for our example We're going to be starting in cell a1 So that's two parts there and then the last thing we're going to do is just go dim start Cells as range So I just now said we're always going to be starting from a1 But I've just brought this in here as well Just one is a helpful reference when we're doing our dynamic ranges and also it'd be beneficial to you as well If your table fragment say starts in row 7 rather than a1 You can obviously can still use the code that we're looking at here today So now that we've defined all those variables we now need to set them so for our first one We're going to set WS1 as this workbook dot sheets Sheet one so we'll set it to sheet number one obviously you might have actual sheet names Oh, and I've put as rather than equals so there's my mistake So you might have different names, but I'm just stuck with the basic sheet one and obviously sheet two Set WS2 Equals needed again this workbook dot sheets and this one is going to be sheet number two and Lastly, we're going to set our starting cell because that's a range. So we're going to set start cell Equals and we can now use our cell references to the sheet. So it's WS1 dot Range and for us it's going to be a1 But obviously if your table doesn't start a1 you can put whatever range it starts in for you So the next thing we need to do is do our defining of our last row and last column So in essence are we need a piece of code that will say okay Count how many columns there are and then also count how many rows that are so we're going to do last row first so last row Equals and we're going to go WC WS1 because obviously what worksheet number one Dot sells and you might send us do this in previous videos So for some of you this might be familiar But if you need to use in dynamic right or finding Dinef dynamically the last row or column then this is how you do it So WS1 dot rows Count and then start cell and might be easier for me just to type that type this out and then try and explain this after I've done that start column and then Dot ends and then go Excel up Sperga Excel up Dot row yeah, and it seems to have like that and then last column is going to equal WS1 again dot sells open brackets start Cell dot row and then WS1 dot columns count dot ends Excel this time to left Dot column Dot column perfect So what we've basically done here is all the WS1's you can see is referring to our worksheet sheet one Just so everything is constantly being referred back to there You could if you wanted to in what a bit clever you could do it with statement What I'd probably normally do so you don't need to keep making references to WS1 But for the purpose of this video I think it hopefully will the intended purposes by Leaving them in it like it just makes it a bit simpler maybe to read on the screen So the first part as last row so always simply going to do and obviously it looks a lot more complicated than it is is It's obviously all WS1 is referring to sheet number one And then if you try and break it down you say right We're just trying to look at this section here what it's going to do So when you use the cells reference you'd first say your cell Rows so if you're trying to refer to a particular cell you say okay What row is that cell in and then what column is that cell in as separated by this column a column not column sorry comma? So all we're doing is say we want to for this cell We literally want to count the number of rows So obviously just rows count on the sheet and we want to do that count within The start cell column so we know our start cell is in column is in cell a1 Therefore it's going to literally count the number of rows in column a as simple as that and in order for it to do a count All it's going to do is we're going to go literally to the end of the data So find the next blank row and then go up so in theory what it's doing is you know if you do like control down It's in essence. It's just doing that really to identify that the last row is column six If we had a piece of a set of data in the scenario where we maybe had another city here, so Let's say Glasgow, I don't know why it took me so long to think of another city What would happen in this scenario is it wouldn't Identify that column eight or row eight was the last in our data set because there is a gap here So that's just one thing to bear in mind obviously when you're using this process And then the same thing happens for last column in the sense that obviously it's gonna if you look at this one particular section Here it's gonna say okay start in our in our start cell So obviously you start here so in row number one because our reference is row number one or a one And then all it's going to do is count the number of columns And it's literally going to do what seems a bit confusing here because it says excel to left But it's going to literally again just go to the last row in that or last cell in that data set and obviously give us the Column number so last row will give us the number six and last column will give us the number Number 11 because K is the 11th column and I think they're probably explain that probably not very clearly But hopefully at least you understand that it works or in the most basics of senses if you copy this as it is Then you will have what you need Yes, it's one of those ones where sometimes you don't need to know exactly how something works You just know that it does work So as long as you've got that that's the main part and I think we've done the majority of our ground work here So the next part we need to do is set our array. So this part where we had here And this is very simple or we won't this bit is probably not too simple I've got to probably try and explain this again. So we're going to do my array equals So we're basically using the last row and column to give us a dynamic range So my array this part we're now doing is literally just storing all of that information into a simple range That can be stored as the array. So we're going to go WS one range Open brackets. So we're going to start in our starting cell. So this is the first part of a range and Then the second if I start cell correctly and then the second part is then going to be WS one Dot cells and then we're going to go last row comma last column and then close double close dot values, but this one is going to be with a Do like that. So obviously it might help just to write this on here. So when you do a range in In VBA you go range open brackets, and then you can see that you've got your two references So you could even you can even go a one to a five. Obviously, that would be a simple range there Or when you use cells, obviously you can do cells And if you're referring to a particular cell or one particular cell, you'd first put the row reference So you might have row one and then row number five and this would refer to In column or what row number one of column five. So that would be E So I refer to this particular cell what we're doing here with this range Calculation is we're literally saying the first part here is literally what is the first cell? So the first cell in our range is always going to be a one for us because our starting cell Then the comma indicates that we're now talking about what is the last cell in our range So for us we want to say it's this one here in K6 So for that we can easily use our predefined the last row So it's going to say okay, we'll go down to row six So we go there and then the column aspect of that cell is then going to be the last one So then go across to column K and then obviously it's identified that this is our dynamic range And if a more data is in and entered either to the column or the row It's also going to update to that desired spot And the reason I put value to here is if you do value it copies your formatting as well Whereas I'm not too interested in copying the formatting. So I'll just put the number two there So we're only going to take the actual cell contents So this is our array now stored and the very little you think it'd be the hardest part that but the simplest part is actually doing the Transpose so all we need to do now is go my array So referring to that exact same variable if I could spell it correctly my array Equals and we're going to simply go worksheet function dot transpose Open brackets my array Close brackets and that's it So all we've done is my array has first been stored with said what range should be stored in the array and the next part We're kind of overwriting that array by basically saying my array now equals your array having been transposed and that's it So all we now need to do is to go and paste this into sheet to so when we're working Arrays normally you could obviously copy this data and then you might go copy and they go to sheet to and then just do paste And it would expand all the data what we need to do with the arrays We actually need to pre-define the entire range that we want to paste to if not it will only paste so much data Or it will overspill if we put too much and we can simply do this just by referring again to our last row in columns To give us obviously the extent of how much we need to paste in or how big our range is to put it simply So if we go WS to range Open brackets, so we know the first cell is going to be a one within sheet to so we need to do is go a One and then comma to go onto the last cell within the range and for us It's gonna be WS to dot cell Open brackets, and we now kind of need to spin this round. So because we've transposed our data What was our last row is now the last column if that makes sense And what was the last column is now the last row because you imagine everything's been flipped around So this is why I'm doing it kind of backwards as it would look so last row for us is now going to be the last column and The column index is going to be last row Close brackets dot value equals my array Oh, and I've done something wrong there. So let's have a look. Let's go range Open brackets open brackets close up. There should mean double closed brackets at the end here Go into that and this should now be the extent of our code So is a lot of content in time still see in terms of actually putting that in and explaining it Of course I look at the time I think I've been going for nearly 20 minutes already so apologies for that But you can see that the actual code itself is is quite basic really there isn't too much of it So what should happen now? So if we run this code simply by clicking within the subroutine in F5 You can see that our data has now been transposed over So I'll start in position in sheet one was here and you can now see in sheet two our data has been transposed over for us Obviously the date format has not been kept there So we can do is go short day But you could obviously build that into your macro if you so desired if you now just delete this go back to our sheet And let's add another couple of cities. So let's say city one city to City three and let's add some more dates in here as well. So let's say take this up to the or got 13 go to the maybe 14 Move across here. So I'm seeing I'm doing and let's just copy all this data across here Like so and then we'll do the same here. Let's just copy all this down as well. I think I went down three Perfect. So we've had a new data So obviously our macro should now pick that up because what dynamic ranges back to our subroutine hit F5 And you can see it has now obviously picked up those new cities and the new data if we obviously change this to a short date Yep, so we can now see we've got data also for the 11th 12 13th and 14th and November So I'm sure many of you are familiar with doing that manually But hopefully this now gives you a dynamic solution to obviously remove that manual process and automate The transposon of data using vba. If you did enjoy that video, please do make sure you give the video a like And don't forget to subscribe to our channel as there'll be a follow-up video Coming on very soon following this video in which we'll look at how you can use the same process But with another simple couple lines of code you can actually reorder these columns Super quickly. So do make sure you subscribe to the channel and hit that bell notification So you're notified as soon as that video comes out. Thank you very much for watching and we'll see you in the next video