Excel VBA Introduction Part 51.1 - Introduction to Pivot Tables in VBA

welcome to this wise L excel vba tutorial this video provides you with a basic introduction to working with pivot tables in VBA so it really is designed just to give you the absolute basics of generating your first pivot tables using VBA code we're going to start the video with a quick look at how to create something called the pivot cash which is something you're going to need to do if you want to create pivot tables at all once we've created the cash we'll show you a couple of different techniques you can use to generate pivot tables using that cash and then once we have the table created we'll explain how you can assign fields to the various areas of the pivot table once we have fields in place we'll explain a couple of techniques you can use to modify field properties and then the last part of the video is going to explain how you can apply filters to the various fields in the pivot table so let's get started okay so here's the starting point for this video is just a basic workbook full of information about various different movies if you want to download a copy of this file then you'll find a link in the description below the video if you're watching this on youtube and if you're watching otherwise our website then you should find the link just above the video what we'll do to begin with is create a very basic pivot table using some simple VBA techniques so to get started we'll head to the Developer tab and head into the visual basic editor of course then we'll need to insert a new module and then we'll create a quick new subroutine here called create pivot table and something as well called a pivot cash a pivot cash is essentially just a copy of the source data that you want to base your pivot tables on the pivot cash is optimizing the such way that it makes the pivot tables themselves incredibly quick to perform their tasks and you can create a pivot table without having the pivot cash that's going to be our first job we'll start by declaring a variable to hold a reference to the pivot cash will create I'm going to call mine PC as a pivot cash now pivot cashes are objects which belong to the entire workbook object so to set my PC to be equal to a new pivot cash we need to refer to a workbook in this case I'm going to say this workbook dot pivot cashes and then I'm going to use the create method on that collection now you can create your pivot cash on a variety of different types of data source so for instance you could connect to an external Access database or maybe a sequel server database and then create pivot tables based on that data we will look at some of those more exotic external database types in later videos in the series but for this particular video I want to focus on just working with data that's stored inside the same excel workbook as the pivot table itself so for that reason I'm going to set my source type to be equal to excel database so to make that work I'm going to use a space underscore and I'm going to name my parameter here as a source type colon equals Excel database we'll use Excel external one we want to connect to an external database as we'll talk about in later videos the next thing I can do is specify the source data so let's name the source data parameter as well and for a range of Excel cells this is really just reference to that range now ordinarily I would use an actual range references so I start with range and then open the parentheses and refer to the range of cells that I want to base my pivot cache on but Microsoft's documentation recommends that you don't use a range reference as the source data argument they recommend instead just using a string of text which represents the address of the cells so I could say for instance in this case in a set of double quotes I could say movies exclamation mark and then I could say a1 - oh it's about 365 rows down that particular workbook so if I did that that would refer to the correct range of cells that's a pretty poor way to refer to the range of cells what if I changed the name of my worksheet or I changed the range of cells in the worksheet don't want to base my pivot cash on let's do something a little bit cleverer instead rather than referring to that literal string what I'm going to do is use the code name of my worksheet which is much less likely to change I'm going to say WS movies dot name and then I'm going to concatenate in an exclamation mark and then I'm going to concatenate in the address of the range of cells for the entire table of data on that sheet so I'm gonna say WS movies dot range a1 dot Curren region dot address so that will construct the correct reference sorry not add comment addresses what I meant to say there so that will construct the complete cell references for the range of cells that I would like to select now the final parameter here called version lets me control which type of pivot table is going to be created from this pivot cache so it lets you essentially set the version of your pivot tables to be related to a particular version of Excel so if I say version colon equals then I press ctrl + space on the keyboard and look for Excel pivot table version you'll see there's a variety of items in this list and the exact list you'll see here will depend somewhat on which version of Excel you're working in so I'm working in Excel 2013 for this video which means the latest version that I have available here says pivot table version 15 so 15 refers to the internal version number of Microsoft Office so 15 for Excel 2013 14 for Excel 2010 12 for Excel 2007 so they were slightly superstitious and they skipped over the version number 13 11 is back into Excel 2003 I think 10 is office XP and if you're a really really really old-fashioned there should even be an item in this list for a pivot table version mm or referring back to Excel 2000 as in searly hope you're not still supporting Excel 2000 in your office but if you are then you've got the opportunity to create pivot tables using that version I think it makes sense to just go with the latest version I have available if you omit this parameter then in this version of Excel it will default bizarrely to pivot table version 12 so Excel 2007 s version now I'm pretty certain there aren't that many differences at all between versions 12 14 and 15 but just for the purposes of this video I'm going to go with version 15 then closer parenthesis and that's my pivot cash created now I've said that I've just created my pivot cache but that's not technically speaking quite true the pivot cache won't exist until I create a pivot table based on the pivot cache so just to demonstrate that that's a case I'm going to write a debug print statement and print out the number of pivot caches in the entire workbook so if I say this workbook dots pivots caches dot count and then if I display the immediate window by pressing ctrl + G then head back to their code and then press f5 to execute it you'll see that I've actually got zero pivot caches in the entire book even though I've apparently created one in the immediately preceding statement so just to prove that the pivot cache will be created when I create the pivot table let's add in a new variable up at the top I'm going to say in PT as pivot table which is going to be used to hold a reference to the table I'll create I'll then want to create a new worksheet to add the pivot table to so a bit further down let's say worksheets and dots add and I'm going to make sure that I've selected range a3 on that sheet I'm going to use range a3 as the cell the top left hand corner cell for my pivot table there are several different methods you can use to create a pivot table but a quick convenient one we can use right here is to use a method of the pivot cache so I say PC dot create pivot table that will allow me to generate a pivot table based on that pivot cache I'll show you the other technique in just a moment there are a couple of parameters we can fill in here let's fill in the table destination that one's quite quite easy the table destination will be either cell that I've just selected on the new worksheet I've just created so I can reference that as active cell I can then specify a table name as well this is optional and it will get a default name like pivot table 1 which is fairly boring and give it a slightly more descriptive name let's call it movie pivot that just helped me to reference it later on and I need to do things to the pivot table ok so at that point I can just repeat the debug print statement from earlier on and I might print some other information about my pivot cache as well so I'll have another debug print statement that will say something like PC dots memory used and let's have also PC record count or something that's all the yes record counters or unwanted and then let's say also PC dots version just to print out the information about which version eyes just to prove that that can change as well so I'll just clear out the contents of the immediate window and then if I execute this code at this point I'll end up with a new worksheet to put in see I've printed out zero paper caches and then I created one pivot cache after I've created the pivot table and it tells me how much memory it uses in bytes how many rows of data are involved and what the type of pivot table is what version it is so it's number five the pivot table itself will be sitting there on the worksheet that I've just created and you see it's name is listed there as well it says movie pivots in little box there I've got access to all of the columns all the pivot table fields from the original worksheet just to demonstrate a slightly different way to create a pivot table I'm going to make a complete copy of this entire subroutine and then paste it in just down below and then I'm going to change the name of this one so that it's called add pivot table and the pivot cash as well let's create a new pivot cash so I'll use the same variables as earlier on I'm also going to declare a new variable that refers to the worksheet I'm about to add so with a dim WS as worksheet I'm then going to use that variable to set a reference to the worksheet that I've just added some as a set WS equals worksheets bad I'm just going to take away the first debug print statement there as well so that I don't print out the same information lots and lots of times then I've created the new paper cash at the top here and to create the new pivot table what I'm going to do instead of applying the create pivot table method to the pivot cash I'm going to refer to the WS variable the worksheet and refer to the pivot tables collection so each worksheet has its own separate collection of pivot tables so I can say pivot tables dot add now you'd hopefully see the slightly unfortunate thing thing about this method is that the intellisense completely falls over it doesn't work at all for the add method of pivot tables so you kind of have to know the parameter list for this which fortunately I do so the first parameter is pivot cash if I can spell that properly that would help as well that x charm there we go pivot cash column equals and then I can specify that that is equal to the PC variable so the pivot cash that I've just created the next parameter I can fill in will be the table destination so again table destination will be the cell that I've just selected on this worksheet at table destination colony calls active cell followed by one last parameter which is table name skimming up that's been underscore rather than a plus table name colon equals let's call this one movie pivot to nothing if not inventive okay so if I close up parenthesis at that point that will be another way to add a pivot table to a worksheet just to quickly demonstrate how that works I'm going to clear out the contents at the immediate window and then run that subroutine one more time and we'll see that this sum I now have two separate pivot caches in the entire workbook but each one uses the same amount of memory with the same number of rows and has the same type or version so those are two fairly quick simple methods for adding a new pivot table there's even a third method which uses the pivot table wizard but I'm not going to bother talking about that one I think two separate methods will give you more than enough choice for for achieving the end results you want it really doesn't matter which of these two techniques you choose to use personally I prefer the PC create pivot table because you get the intellisense for the the parameter list but it really doesn't matter in the long run which of these two different techniques you use one thing that is worthwhile mentioning is that we could have referred to a one of the existing pivot caches rather than created a new one in each of these separate subroutines so as long as the worksheets on which the pivot tables exists still there in the pivot table itself hasn't been deleted then the pivot cache will remain in place as well so what I could do is write a new subroutine or in fact let's just cheat and copy and paste the first one that we created and if I paste that one in down at the bottom I can change his name so create a pivot table and using existing pivot cache and then I can just make sure that I've swapped that properly so the pivot cache I'm going to set this time isn't going to be equal to a new pivot cache all I'm going to do here is set PC to be equal to this workbook dots pivot caches one so the pivot caches collection of the workbook is a one based index so I've currently got two if I wasn't sure that my pivot cache existed or not I could do a quick simple test I could say if this workbook top pivot cache count equals zero then create a new paper cache otherwise use the existing one what I could then do is well I don't really need to do anything else at all I've got all the other bits of the code set up already so I'm just referring to a pivot cache that already exists rather than creating a brand new one let me just remove that original debug print statement and I'll live in the other two down below just to prove that when I run this one I'll only end up with two pivot caches rather than three as I would have had I created a brand new one so still got both the original two paper caches I've created but this pivot table now is based on an existing pivot cache rather than creating a new one from scratch now as I said you do have to be slightly careful with this technique to ensure that at least one pivot cache exists before you try to reference it so let's just say we wanted to quickly delete all the worksheets except for the movies sheet in this workbook I'm going to write a quick subroutine to do that let's call this sub delete all but movies sheet then we'll do this with a quick symbol for each loop so I'm going to say dim WS as worksheet and then say for each WS in and this workbook dot worksheets I'm going to write the next WS line and then inside the loop I'm going to say if not ws is WS movies as the code name of the the movies worksheet then I would like to say WS dot delete and before I tend to do all of that I'd like to add a quick statement at the top that says application dot display alerts equals false so that I won't get the warning that I'd ordinarily see whenever I try to remove a worksheet so having written that if I would try to run the subroutine I'll delete all the worksheets except for my original movie sheet and then if I scroll all the way back down at the bottom of course where I previously referenced my original pivot cash because I've deleted the worksheets and the pivot tables on which they were based on those pivot cashes the pivot cashes themselves have been deleted now as well so if I run this subroutine this will fail because the pivot cash doesn't exist of course a simple way to resolve this would be to count how many paper caches there are in the workbook so I just reset the routine at that point and then have a quick if statement at the top that says if this workbook dot pivot caches dot count equals zero then what I would like to do is set PC to be equal to a new pivot cache so I'm going to copy and paste the statement from earlier on that creates new pivot to cache otherwise what I'm going to do is set the PC to be equal to the existing pivot cache and then say end if then I'll add the new worksheet and then add in the new pivot table so having done that I'm just going to remove the contents of the immediate window I'm printing out the number of pivot caches in the entire workbook so if I were to run this ability at this point I'll get a count of one but if I run it again I'll get another count of one and so on and so on and so on suite subsequent worksheet that I create at this point we'll just use the exact same pivot cache so that saves on memory it reduces the size of your workbook it does mean that if all the worksheets and all the pivot tables share the same pivot cache then of course any changes to the pivot cache will affect all the other pivot tables in the workbook so there are legitimate reasons why you might want to create multiple different pivot caches if you want to be able to update them individually all that let them refer to different ranges of cells of course but in this particular case I think having a single pivot cash for the workbook is a sensible idea I'm just going to tidy up the immediate window first of all and then close it down and then head right back to the very top of this of the module and run my delete all the movies sheet sub routine just to tidy up the entire workbook okay so now that we understand the basic object required to create a pivot table let's have a look at what we can do to manipulate the pivot table object itself I'm going to scroll back down to the bottom of this procedure or both Bossman's module in fact and run this final procedure we created which was creating a pivot table using the existing pivot cache or creating a new one if required so I run this one just to generate a new blank pivot table I want have a look very quickly what you can do in Excel itself to manipulate the pivot table just in case you're not familiar with this so there are four main areas of effect of a pivot table and you should find these listed in the pivot table feel this on the right-hand side of the window if you've got the pivot table selected of course if you click away from that and the feet feel this disappears click back in the pivot table the field list will reappear that's true of you've got the field list turned on of course when the analyze tab in the ribbon there's a field list button which you can use to turn it on and off for the four main areas then we've got row fields column fields values and then filters which are slightly differently so rows and columns and values are items that exist in the actual table itself so if I were to drag in let's say let's say I wanted to see the film's genre in the rows area I can just drag that field in or indeed just have ticked the box and that will generate a list of all of the individual genres for all the films in the list I'd like to add the certificate field to the columns area to generate some column heading this is if I drag certificate into the columns box you'll see again I get a list of column headings based on the individual values in that column now to actually do something useful without to aggregate the information to create a similar on average etc I simply have to take one of the columns let's pick run time for instance and drag that into the values box this is just one way to generate a pivot table by the way there are many other ways you can manipulate these so I'll end up with a summer four in time because the run time column is a number column it automatically generates a sum so if I were to wanted to modify that I can use the field settings option if I click the drop-down arrow next to sum of run time choose value field settings and I'm for instance I could choose an average instead and then click OK and that gives me a data table or a pivot table based on three fields filters is fulfil Turing the entire table so for instance if I wanted to filter this based on I know a particular country for instance I could drag the country column into the filters area then I've got a simple convenient way to apply a filter to the entire table so I could choose New Zealand for instance and click OK that filters the entire table to show me only matching genres and certificates I can change that to any other country of course and click OK I could even select multiple items as well so I check the box I can then choose multiple items there and then click OK to see multiple results so those are the four main areas of the pivot so how they basically operate what I want to see is how we can write code to achieve the same sort of results I'll start by heading back into the VB editor and then I'm going to create a new copy of this subroutine that we've just finished writing so let's copy that one and let's place that in a separate module as well so it's inserting a new module and I'll paste that procedure in I'll change its name so it's called something like editing pivot table and I'll just get rid of all the rest of the name of that to the up towards the end I'm going to remove the debug print statements here as well and assuming that we've now created a pivot table successfully what I then like to do is start manipulating the pivot fields each PivotTable has a collection of pivot fields so once you have a reference to a pivot table and in my case I've got a reference to it by using the variable PT I can refer to the pivot fields method which will allow me to reference any individual column or field that belongs to the pivot table I can do that either by an index number or I can do it by name and by name is far more convenient way to do it so the pivot field names are the names appear exactly as they're shown in the pivot table field list so let's say that I wanted to refer to the genre column first of all I would like to refer to the genre pivot field and number I can do is a variety of things to it but sadly annoyingly the intellisense kind of breaks down here and it doesn't give you much of a clue about what you can do so what you might find useful is declaring a variable which you can use to hold a reference to it each individual pivot field as you work through generating the pivot tables I'm going to say Dimpy F as pivot field and then I'm going to set P F to be equal to PT dot pivot fields genre now you don't have to create the pivot field variable if you don't want to but it does make life way more convenient to the next stage when you want to manipulate that pivot field if I now say P f dot I'll get a full list of all of its methods and properties and for me that's well worth the effort of creating the pivot pivot field variable in the first place so the first property I'm going to change here is the orientation property of the pivot field I'm going to make this one equal to the excel row field so this will mean that the genre will appear in the left-hand column of the pivot table what I'm then going to do is just copy and paste those two lines because I'll need to essentially the same thing for each of the other three areas of the pivot table that I want to populate so for the next thing I'll say let's set this to eat your two certificate and then I'll set the orientation to be equal to excel column field if I paste those two lines in again I'll set the next pivot field to be equal to what we use the run time last time let's use something different let's use Oscar wins this time I'm going to set this to be equal to an excel data field you can have more than one of each of these items by the way and we'll talk about that in just a moment but for the time being we'll just stick to one field in each of the PivotTable well paste the code in one more time and then let's say let's go for the country field again I'll set this one to be equal to an excel page field okay so having done all that let's just run the entire subroutine and see that we'll end up with yet another new worksheet which we'll have in this case genres down the left-hand side the certificates across the top row the summer Bhaskar wins is the data area and then the country filter right at the top of the pivot table so that's how simple it is to manipulate the pivot fields in a pivot table okay so that was a fairly simplistic way to achieve the end results that we wanted but it was quite a lot of code to write just to add four fields to the various different areas of the pivot table there is a slightly quicker way to achieve the same end result if you don't want to have to write out all of these lines of code I just slept those lines and comment them out for the time being I'm going to say PT dot add fields this is a simpler method to use now lets you write a comma-separated list of row fields column fields and page fields not data fields we'll have to deal with those separately in just a moment but for the row fields all I need to do is specify the name of the row fields which in this case would be Chandra and then in a comma and I can say the column field would be certificate and then the page fields in this case I'm going to set to be country to set a data field as well I'll need to say PT dot add data field so again there's a separate method for allowing that so I've got to refer to the field as an object I can't just put in the fields name this time you'll see that it says field as object so in that case I would have to say something like PT dot pivot fields and then open some parentheses and let's go for Oscar wins again and then you'll notice that I can also specify the caption this so that is the name that will appear in the top left-hand corner of the pivot table I'll leave that as is for the time being much more usefully I can also modify the function that's applied so by default in number field will assign a sum function if I wanted it to assign let's say an average function I can say excel average to assign that function instead so much less code to write to achieve exactly the same result with the extra added advantage of being able to modify the phone if I were to just run this subroutine at this point we'll end up with a new worksheet with the same row fields column fields and page fields but with a new data field or a new function applied to the data field it is of course possible to reference and modify any field once it's been added to the pivot table so let's say for instance I wanted to modify the data fields I wanted to change its function I'm bored of the average at this point let's switch back to the VB editor and I realize that I've only just added in that data field but should I want to modify it I can I can refer to it as part of the data fields collection so as well as an overall pivot fields collection which refers to every single field available to the pivot table the data fields collection refers to just the fields in the data region there's likewise there's a row fields and a column fields property that you need to refer to just the field in that particular area of the pivot table but in this case it was definitely data fields now I can reference that by name but I've got to be wary of this because the name of the data field will be the name that's shown in the top left-hand corner so in this case it will be average of Oscar wins not the original Oscar wins that's easy to get tripped up with now because of that is usually slightly easier to reference a data field by its index number so it's a 1 based index so I can say PT data fields dot and then I could say function you can see again the intellisense is broken down here so it might make more sense just to get a bit of help to say set P F equals PT data fields 1 and then we could say P F dot function equals and we also get an enumerated list of all the available functions so let's go with the let's go with there at the max this time so having done that if I were to exclude this subroutine at this point and then have a look back at Excel I'll get another new pivot table but this time he's got the max loss Quinn's because I've modified that after it was originally the average you can have more than one field in each area of a PivotTable so let's add a couple of fields to the row labels area maybe a couple of fields to the page fields area as well so what might that look like let's say let's have the genre and country in the rows area so that breaks down each genre by countries in which films were made and then we could also have a couple of extra things in the filters area so let's put the let's say we put the distributor in the filters area at the top and let's say also maybe the studio for instance so we can put in combinations of distributors and studios so to make that work what we need to do is specify which exact columns go into each area so if I go back to the VB editor of course and then let's do this very quickly first with the original technique I'm just going to comment out the last four lines that I put in and then uncomment the one we're setting the pivot fields individually so you set the pivot fields to genre and set that to be a row field let's then set it to be the country so in fact I'm just going to quickly steal that line from later on down in the code so I've set the genre to be a row field and then the country to be a row field and then I can say PF dots orientation equals excel row field now what I then need to do is specify which field goes into the page fields and I said I wanted the distributor and this studio in this case so let's just change the line there that says country and then make it the page field let's just set that to be distributor and then make that the page field and then I can just simply copy and paste those two lines and do that again so but this time rather than distributor I'm going to put that in as studio so those are the extra fields I want to specify let's give that a quick test if I run that one have a look back excel and I'll find that my fields have gone in exactly as I've asked for them now you can change the position of the different fields in each area at the pivot table as well so let's say I wanted the country to be ahead of the genre so country on left hand side and genre on the right and maybe also on to the studio to be sitting below the distributor so to make that work I can change the position property of each field so going back to the V biota I've got my country fields added into the rows area let's say I want the country to be the first positioned fields I can say PF dot position equals one so it will move it to the left hand side now currently studio is sitting above distributor I want to position it below now slightly weirdly for the page fields they're numbering system works from the bottom upwards so what I need to do is put the studio's position into position number one rather than position number two as you might expect so I'm going to set the studio field to be PF dot position equals one so having done that if I were to run that subroutine and then have a look at the results I'll end up with studio below and country on the left with each genre inside each country you can achieve the same results using the add fields method as well although it's a little bit trickier to do that cause a little bit more awkward to write for this technique let's switch back to the VB editor and have a look at how it works if I just comment out all of the lines which use the pivot field variable and then uncomment the lines which bring in the add fields method now what we've done previously is specified the row fields column fields and page fields in a sequence like so what I'd like to do now is add multiple fields to the to each item so for the row fields and the page fields I want to put in multiple fields so to make that work and make things a little bit easier to read I'm going to use named parameters and say row fields column equals and then if I want to specify that I have the genre and the country I need to pass them in as an array underneath the array method and then in there I can write in a comma separated list of individual strings so I've got John row and then country and then I can type in another comma and control space space underscore and then I'm gonna specify the column fields which in this case will just be one single column field certificate and then another comma then a space underscore and then to specify the page fields I can say page fields colon equals and then again I've got to use the array method to pass in a comma separated list of strings so let's have the studio and then just to mix it up a bit let's have the language in here as well so just so we can see that we're getting different results each time okay so the code is a little bit more fiddly to write than it was previously but it's still perfectly possible the fields will go in in the sequence you've listed them so Jean will be one that country studio will be at the bottom with language above because studio will be in position number one I've got the separate add data field method in there as well so at that point if I run the subroutine and have a quick look at the results it was big pardon of misspelt column fields there but you guys spotted that about 10 to 20 seconds ago to me sorry column fields not whatever I spelt whatever I said let's just run that one one more time let's give that a quick try and if I switch back into the Beebe editor I've got genres and countries and studios and languages using the different technique so it goes a little bit more fiddly to write but it's still perfectly part well I guess the important thing here is you've got choices about how you achieve the same end result and it's up to you to pick the one that suits you best another thing that I talk briefly about doing was filtering a pivot table so just like to tidy things up a little bit at this point before we go into that little topic and look about some module number one and I'm going to delete all but my movie sheets by running that subregime and you come in handy at some point and then back into module number two I'm going to make a new copy of my editing pivot table subroutines I'm just going to copy the entire thing and then paste it in just down below I'll then change the name with the subroutine of course so I'm going to call this one filtering pivot table and I'm going to change a few different things here to set up a pivot table with a specific range of fields I'm just going to delete all the items just down at the bottom that are currently uncommented and then I'm going to uncomment the commented out lines and I'm going to change things up a little bit so I'm going to make sure that I've got the country back in the page fields first of all so let's change the the pivot feels there to country and then that'll be the page field I'm also only going to have one single item in the row field as well so I'm going to set the that to be the genre again so it's just back to a regular the original pivot table that we started with so at that point I'm just going to run that subroutine to generate that pivot table and make sure that it looks reasonably sensible and then what we'll do at that point is make some modifications to the filters for both the page filter and then the row and column labels as well so back into the VB editor I'm going to create a new version of this subroutine I'm going to create a new sub called filter existing table so I appreciate that I called the previous one filter pivot table that was really just to set up the other pivot table I want to separate this code out into a new routine so filter existing table and then I'm going to declare a variable called PT as pivot table and I'm going to set this pivot table variable to refer to the first pivot table on this sheet so one way to do that it's a set PT equals active sheet and dot pivot tables I can spell that pivot tables and I can just reference it as is its foot as the index number one if I know that it's got a unique name as well I can reference it by name so I gave the pivot table when I created it in this case the name movie' pivot so rather than using the number one I could just use the name movie' pairs instead so that's entirely up to you once I've got that reference what I can now do is start modifying the visibility of various items in the pivot table so I'm going to declare another couple of variables to do that I'm going to call 1pf as pivot field just as we were doing earlier on and then I'm also going to create one called P I so dim P I as sorry like I've spelled P hi at this point P I as pivot item so a pivot item represents a single individual value in a single field so at that point are going to say set P F equals first of all I'm going to set P F equal to P T dot pivot fields open some parentheses and double quotes the genre and then I'm going to set P I equal to one of the specific items in that field now to refer to a single specific item in a pivot field you need to know the values of those items so I just switched back into Excel I can see in the pivot field called genre I've got about 20 different items or so I want to refer to one single specific one I'm going to pick romance because I don't particularly enjoy romantic films I want to make them invisible I want to filter those ones out so to do that I need to go back to the BB error and set P I equals one like I'll even spell P I again set P I equals p f dot pivot items and then in a set of parentheses I can refer to the individual specific one again either by its index number or more usefully in this case by name I can say romance and then I can do anything to that single individual item that I want to pass API dot I'll get a full list of all of its methods and properties so what I'm going to do is change this visible property in this case I'm going to say P I drop visible equals false and simply having done that if I were to run that subroutine at that point switch back to the workbook that I was just looking at I'll find that other romantic films have been filtered out at my list neezy the little filter icon has turned on at the top of that column now should I want to bring that item back of course I can just make it visible property equal to true again put it over if I've applied lots of filters at the same time perhaps I wanted to bring back all the genres so that's such a seven checked a few extra genres here as well I want to bring everything back from that particular field to make that work I can clear the filters from a particular pivot field if I go back to the VP editor I'm just going to quickly comment out the two lines I used to make romantic films invisible and then I can simply add in a new line below that says PF dot clear all filters if I do that and then just execute the subroutine and then switch back to the Excel workbook I find that all my Shamas have reappeared now it might be that I want to filter out items based on other criteria than just the values in that particular column so let's say for instance if I change my Oscar wins some to account instead that will effectively tell me how many rows there are in each genre so I look at the grand total over there that song how many rows there are in each individual genre let's say I wanted to filter out anything that hasn't got at least ten films in that genre so if the genre the grand total is is ten or more and it should be visible otherwise it should be invisible now in order to make that work what I should really be doing is looping over the pivot items collection so let's have a quick look at doing that if I go back into the same subroutine I'm just going to comment out the clear all filters line just for the moment and then rather than setting P I equal to a specific item in that field what I'm going to do instead if I were to move that line is I'm going to say for each P I in P F dot pivot items now what I can do is test the record counter the pivot item so if I say if P I dot black code count is greater than or equal to ten then I want to make that particular item visible so I'm going to say P ID not visible equals like Vampira all visible that was meant to be equals true else I would like to make P I've Isabel equals false so I'm to come to uncomment the line that I just added in there and of course I'll indent that to the correct level and then say end if and then finally say next P ice which is the generic basic for each loop I could do that to loop over all of the items in a field I could leave all the fields in a pivot table as well using a for each loop any we haven't done that if I were to just run that subroutine at that point have a look back at the Excel workbook I'll find that I've now only got items if they've got at least ten records in that particular category now although that technique works and there are certainly useful reasons for looping over collections of pivot items there is a slightly easy way to achieve the same result as the one we just looked at here if I take you back to the BB editor and I'm just going to comment out all of the loop that we just written previously and then I'm going to uncomment the clear all filters and then just run that one just to bring everything back again in the same pivot table and then what I'm going to do is after I've cleared all the filters I'm going to say PF dot pivot filters dot add now this might be adding your version of Excel in this particular version of Excel it's called add two but it doesn't really matter because the method works in essentially the same way you've got to choose a particular type of filter at this point so there are some really useful things you can do here with dates we're going to talk about dates in a slightly different video as sort of separately and variety of other techniques and features of pivot tables to cover in different videos in this series so what I'm going to do here is say Excel value greater than or equal to and then type in a comma then the data field that I'd like to apply that to is the first data field of this pivot table so I can say PT dot data fields open some parentheses one then the value that I'd like to apply to that is a value of ten so just the same one that we've just done effectively so just to show you at this point if I just restore down the VBA editor window just to be sure that I've got all the genres back at this point and then if I were to just run this subroutine I would end up with essentially the same results I've just seen previously well the grand total has a value greater than or equal to ten one type of filter we haven't looked at yet are the page filters these work slightly differently depending on whether you're using a single item or multiple items in the page filters just to quickly show you how that works I'm going to change my pivot table back to allowing or showing all fields I'll I'll manually just clear all the filters from genre and then I switch back to the VB editor I'll just modify the code a little bit to set it up for working with the country field it's going to set PF equal to the country pivot field then I'll click all the filters from it and I'm just going to comment out the pivot filters dots add to method then what I can do is say PF dot current page so if I'm looking for a page filter and I've got a single item that I want to select I can simply say current page equals and then I can refer to one of the countries in the list so let's just say something like let's go for New Zealand first of all I just restored down the window at that point and then I can run that subroutine and I'll see that I get the entire page filtered just for New Zealand I can change that country to any other one that I want and then that subroutine as many different times using a different value let's go for United States they'll be a bit more data there Hollywood and all that so there we go if I wanted to allow multiple items to be selected in the page filter the mouse starts to operate a little bit more like the individual pivots items filters that we love that earlier on so to do that in the first place I've got to enable multiple items to be selected so let's just comment out the PF current page line and after I've cleared all the filters from the pivot field I'm going to say PF dot enable multiple page items and I'll set that to be equal to true then at that point is just a case of referring to the individual pivot items in that page field so rather than using my pivot item variable this time I can simply say things like let's say PPF dot pivot items and then I'm going to refer specifically to United States again but this I'm going to make United States equal to false and then set that to be equal to false that's a big fun but visible equals false of course so let's refer to the actual property use power the unchanging the visible equals false and then I can do exactly the same thing let's go for the United Kingdom so let's turn off the United Kingdom this by far away the two biggest movie producers in this particular database so let's go for United Kingdom and setting that visible to false I haven't spelt visible correctly that would have been a bit of a disaster had I copied and pasted that there we go alright so having done all of that if I now run that subroutine I'll find that I've eliminated United Kingdom and United States so essentially behaves exactly like the standard row labels and column labels filters when you refer to an individual pivot item okay well that's just about wraps it up for this first introductory video on pivot tables in VBA you've seen all the basics in this video how to get started with the pivot table basically so you now know how to create a pivot cash and then create pivot tables based on that pivot cash you've seen how to add fields to the various areas of a pivot pivot table so the row fields column fields data fields and page fields you've also seen how you can manipulate those fields in terms of modifying which function is applied to a data field the position of multiple row fields and page fields and also finally how you can apply filters to any field in a pivot table the next few videos in the series will go into more detail and slightly more exciting things now that you've got the basics down so I'll see how to connect to external data sources will look at slicers drill down features and a variety with the techniques as well hope you've enjoyed this one anyways enough to get you started thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 79,833

Rating: 4.9602838 out of 5

Keywords: excel vba, visual basic for applications, pivot table, pivot cache, pivot field, pivot item, pivot, wise owl, pivot filter, filter pivot table, create pivot

Id: agQAda7HphQ

Length: 44min 28sec (2668 seconds)

Published: Tue Nov 22 2016