Excel Macro - Pivot Table, Dynamic Range, Error Handling - Excel VBA Part 11

all right in this video we're gonna be creating a macro to make a pivot table so we'll just start with the recording and then we'll see what potential problems we'll have without recording and try to fix those to make our macro work in different cases so let's start by taking this data and quickly creating a pivot table out of it I'm gonna go under Developer tab record macro I'm gonna call this create pivot hit ok now we're recording so I'm not gonna explain what pivot tables are if you don't already know in this video in this one I'm just gonna go ahead go on there insert click pivot table it picks up the data I'm gonna press ok now here I'm gonna put sales in values sales reps in grows and region in columns I'll also right-click on one of these values go under number format apply some formatting so I'll do currency and hit okay so by the way it's important to change the formatting this way instead of trying to just highlight and change the formatting you want to change the formatting for the field in the pivot table not for the range in Excel so this is done I'm gonna go to Developer tab and stop this recording now I'm gonna go back to my data and try to run this again and you'll see we got an error so let's try to see why we're getting this error if you look in this particular macro the first line is gonna create a new worksheet so that adds and you worksheet for us in this case that ended up being sheet 3 now the next line here creates the pivot table and to create a pivot table here if we look we provide the source data which has the data worksheet and then it has this R 1 C 1 syntax which points to from a1 through column 6 and 20 second row that's the data source and then the destination for this pivot table is gonna be on sheet2 the first column third rows so that's a three so she to was the worksheet that we used to have when we used to record this macro but now when we run it again it should be basically creating this on this sheet three not on sheet 2 so our destination now is incorrect and then it continues by again selecting that sheet 2 which is again it's gonna be incorrect worksheet and selects this cell and then we start basically working with that pivot table and adding fields to that pivot table and formatting all right so now we figured out that we have to make sure that we point to the right worksheet because as we add another worksheet the new worksheets name is not necessarily gonna be sheet - it could be sheep three sheep four who knows so what I'm gonna do on top of this I'm gonna make sure that after we add our worksheet then we'll just get the name of our current worksheet and the way we can do that we can make a variable call it like pivot WS and then we'll set it to so we do active sheet that will be our current worksheet and name so that will get us the name of our current worksheet so now we'll have this pivot worksheet and what I'm gonna do I'm just gonna copy that name and see how here it says to go to sheet 2 instead of doing that sheet 2 I'm just gonna replace it without quotes pivot WS because this is a variable should not be in quotes so basically this would select the new worksheet that was created and in a similar way see this sheet - we don't want it to be sheet 2 so again that's text I'm gonna remove that part from that text that's the beginning until that exclamation sign which is you know the separator between the worksheet name and the range and before that I'm gonna put my variable and concatenate it with the rest of this so this will be the name of our current worksheet and then we'll do this so there are other ways of handling this and we'll talk about those when we get to next level series but for now let's try to check this and see how this works so I'm gonna go ahead and get back to this open my data tab this is it I'm gonna go through macros and run the same thing let's see this time we were able to create that new pivot table on our sheet 4 even though the name of this worksheet is different we're able to dynamically get that and work with that so that's one piece with it let's go back so the next thing I want to do here I want to make my data range more dynamic so if this data had more or less rows I want to be able to still make my pivot table now right now as is it's just this you know from the first road to 22nd row now to make my life easy the first thing I'm gonna try to do I'm gonna try to replace this r1 c1 syntax with the regular Excel syntax so I'm just gonna replace that with a 1 colon and what's the last column there F f-22 so now instead of R 1 C 1 what if we try to use regular Excel a1 notation let's try to run this macro and see if it still works and as you can see we're good it still picks up the data so I'm gonna go back to that visual basic so now that we got this going we need to now get rid of this 22 and replace it with our last row now the last row for this should be coming from our original data worksheet so when we start running this macro initially we're gonna be on this data tab so this is where we need to get the last row so that means before we add the worksheet we need to get the last row of our data and again you'll choose the best way to get the last row I did use the fine method for the previous few videos for this particular one I'm gonna use control up for the first column method so I'll do cells rows that count and then the first column so we'll go to the bottom of the first column and then we'll do control up which is Excel up and then we'll get the row number so again if you're more interested in other ways of finding the last row I have a very long video covering all different ways and explaining the differences for this one I'm just gonna do this this should get us the last row so this point I'm gonna go here remove this 22 like we've been doing before and just concatenate that L R to it so now that I have this I should be able to add more data and now it should go until 42 and go back and run my macro let's just check if it's pointing to the right range so I'm gonna go change data source and you can see see it goes to 42 so that works now we're able to move this to the last row and again the same way it should work if we have less so if we have like 18 it doesn't matter because we should be finding the last row and it should just work out nicely so I'm gonna go ahead and run this so let's go check change data source see 18 cool so that works so now we made it more dynamic to work with different ranges of data now there's one last thing we're gonna change in this particular macro and that is this worksheet right now in our code see it points to that data worksheet with the name data now assuming that when you get your data this worksheet is always going to be having the name data that's fine but if this name where the change and we call this anything other than data something like this if I go back and try to run this macro see it's gonna fail because it's trying to get this data worksheet source but there is no worksheet like that so it just fails running the rest of this so what I want to do I'm gonna go back to this data one I want to first make sure that I get the name of this worksheet where the data is supposed to come from so I'm gonna get the last row of that worksheet and I'm also gonna get the name of that worksheet I'm gonna call this data WS the variable name for data worksheet and that worksheet at that point is gonna be the active one which means we can do active sheet dot name to get the name of that worksheet so now this will be our data worksheet originally we'll store it in a variable and this after we add this other worksheet this will be that worksheet where we gonna do the pivot table so I'm gonna take that variable and right here instead of hard-coding data as the worksheet name I'm gonna do this and then we'll see the problem with that and fix that too I'm gonna do this so I'm gonna go ahead and try to run this and we'll see what happens and then we'll react so I'm gonna go back and run this macro interestingly enough it says one of the pivot table field names is invalid did I go to the data tab first let's try it again that's weird so it gives us an error with the column names but that's not what our problem is what is happening right now is our worksheet name for this data has a space in it so if I remove that space this macro will probably work so if I do this see it's still not called data let me just do something like this and go back and run this and you can see it works but what happens when your worksheet for your data has some spaces I'm gonna remove some of this okay so when you have spaces in there your worksheet syntax is gonna change so the way this should work I'm gonna remove this for a second and get back to our regular text here when it's just one word you just do data but when there are spaces you have to do this single quotes around that name to have correct syntax so interestingly enough if you have just one word and you still do those single codes it will still work but if you don't do single codes for something that has a space it's gonna fail so what we're going to do we need to incorporate that in our code so we need to replace this part but we have to make sure in the beginning there is a an apostrophe and in the end there is an apostrophe too so I'm gonna go ahead and copy this variable for worksheet I'm gonna just go here right after this first apostrophe I'm gonna close that and concatenate that LR variable and then I'm gonna concatenate again open my text again and remove all of these from here so basically I'm adding an apostrophe in front then our variable which is the name of our worksheet and then an apostrophe and the rest of this range which is then also adding the last row and that should create our worksheet name again when we get to more advanced VBA coding there are more cleaner and nicer ways of doing this but for now we'll just stick to this so I'm gonna go macros run oh of course I put the last row here instead of putting the name of the worksheet that should be the name of the worksheet this is the last row okay fair enough I'm gonna run this again go back and run it here we go we add a space it worked out fine we have our pivot table let's remove the space from this to make sure that also works with this all fine so now we have a working pivot table macro so one last thing I'm gonna add to this I want to make sure that we have data before we make a pivot table so before we run the rest of this macro when we get the last row in our data which is gonna be our worksheet right here we want to make sure that the last row is greater than 1 because if you just have one row on top that means you're not gonna be able to make a pivot table from that and here we're gonna add an if statement we're gonna say if LR is less than 2 then and if and we're gonna say exit sup so we're gonna terminate this subroutine and maybe it will add a message here well do a message box will say must have data for a pivot table so that will be our error message this should be msg there it is so now if we are let's say on this worksheet and we try to run this see it says must have data to do a pivot table another thing we could do we could also get the last column and make sure that we have six columns at least to make this right or also check some sort of columns things so I could also do that control left in the first row and make sure we get here because right now if we try to run this for example on this one it's gonna think that the last row is 13 and it's still gonna run this macro on this worksheet which is gonna fail so again these are optional steps we basically add to do some error handling so I'm gonna do something to find the last column so I'll do the first row and we'll do columns that count and then we'll do end Excel to left and we'll get the column number again the method I'm using to find the last row and last column is control up and control left and control left is going to happen the first row meaning if we do it for example on this particular worksheet we're gonna end up in the first column but if we do it in this one in the first row we will end up in column number six so again now we can say if the last row is less than two or our last column is less than and again you'll have to pick your number I'm gonna say less than six then we want to give this error message otherwise we're gonna run the rest of this macro so now what would happen if I go for example on this tab and run this it says must have data for a pivot table if I go to a blank worksheet and I run this still get the error message if I go to my datasheet and I run this round's just fine I'm gonna get rid of all this comments here and that's pretty much it thanks for watching please subscribe and I'll see you next video

Channel: Learn Google Sheets & Excel Spreadsheets

Views: 37,050

Keywords: Excel, Macro, Pivot, Table, Dynamic, Range, Error, VBA

Id: HC0Qypx8THM

Length: 16min 39sec (999 seconds)

Published: Thu Jan 09 2020