Possible so everyone can follow along before we begin if you haven't please do not forget to subscribe for latest updates and also to support the channel let's get started so this is the sample expense data we'll be using for demonstration it has about 485 rows of data a basic data set again a region just for the u.s departments and then we have uh compensation expense type and then we.
Have the sub items and then these are all the actuals and the period we're ranging from 2006 till 2021 okay um just before we dive in to writing the code let's create a sample pivot table just to get ourselves familiarized with some of the objects um so quickly here are the filter you know like filters um here so for example let's say we want to have a category item or you want to summarize by departments in rows.
Maybe period in columns and then amount in values for example yeah so typical uh pivot table and um so as you can see how easy it is to you know like simply drag and drop and then you are able to see uh all the items summarized in just just like that without any you know like and moreover with uh now that excel has 64-bit version so more capacity as well.
Okay um and let me just bring this back here like this and so this is typically how pivot table looks like um let me insert some slicers so that um we can see how it works in the code and then maybe a timeline as well so if you have a property data set in your data set uh then you'll be able to use.
This as well and it's very handy when it comes to filler filtering of huge data sets so for example years and you're able to filter out just like that and then let's say we want to expand yeah um okay so that will be pretty much and uh so the objects uh model here will be basically the words uh the worksheet as you can see and then from the worksheet you'll have a pivot.
Um it um so record micro i'm gonna select the table um this is just a range but in the table format and i'm gonna click on create we'll talk about the data model at a later stage uh click on create and if we go back to the code here uh you'll see that it has added a new sheet and then created a pivot table and then all the properties are here yeah now i'm gonna get rid of all.
This and then if i bring in for example period here maybe region department category department down here let me keep it similar to the ones we just created so that we can compare it okay so it automatically created all these uh sub data points for us so which is quite useful again um if we go back here you'll see.
That it generated all these codes for us which we can you know like clean it up really quickly um okay so i'm gonna stop recording or uh no maybe i can also insert a calculated field so that we can see how it looks like so let's see that uh amount of sample caulk um i'm gonna say insert this filled amount into maybe three or something like that okay once you're done with your uh.
Formula you can use eve and all this as well so once you're done you'll see once you click on add you'll be able to see that field in the list click on ok and you are able to use that in your pivot table as well okay and if we go back to the code you'll see that it has inserted that as well okay and then it automatically inserted that again into this values um okay so i think that will be all for.
Now let me stop the recording and quickly clean up uh some of the issues that you might face using this will be um if the version is different or if you do not handle the pivot names properly it might conflict with the existing one and give you error and so on so let's do um in a better way um by cleaning this up so first let's create a pivot cache and we'll call this.
[Music] pivot cache and then we'll say set
The cache equal to activeworkbook.cache.create and here we will you specify excel database uh as you know and there are all the uh some videos in the channel as well you can connect to sql server access database tables or views and so on so.Please check it out uh for today's video i'm just going to concentrate more on excel okay and then here you can specify the source and the version you can leave it so that it automatically picks up the version whoever uses so this example will be more of um creating pivot on offline yeah so i'll call this data source and we'll create a new source and we'll say this source equal to our data is in this uh sheet then.
Um or maybe we'll rename shipping to data expands data expensive.range we can take the range from here i'll just select the entire table and say selection dot address so this is going to be our data source here we've created the paper cache and.
Here maybe we can also introduce a new worksheet where we want to add the pivot table okay so this equal to this and now i'm going to get rid of this part and um donate this so here we've created a sheet and then here we will add a pivot table now so um we'll say pts pivot table.
And here set pivot table equal to pivot cache dot create pivot table and we can specify where we want to add a table in the sheet if you want to add in the existing sheet then this is where you will specify for example um here we are adding a new sheet but if you want to add it to the existing one you can do you can specify the name of the sheet.
For example sheet1 something like that yeah um comment this out because i want to create this one runtime to a new sheet and we want to maybe print it in row number four or three of column one or you can also use range format as well okay now just to not prolong it too much this will be to create a pivot table let me just delete this and run.
To cursor run to cursor ctrl f8 so it has created a new sheet and then if i press f8 insert a new pivot table and if i check the source the source is fine yeah uh now we can execute the rest but.
Before that let me quickly uh fix this instead of all this i'm gonna change it to pt within this current module okay and we can simply do in this fashion so that it looks shorter cleaner um we don't need this so we are we don't need the region as well department can be there in the page uh no let's put the category in page.
So we're putting a period in columns that is good in columns we put in the period and then in category is going to categorically to the page uh filters yeah and then department will go to the rows dot and then the region can be so if you have to remove it then this is the syntax to refer to.
Um if you already have a pivot table and you want to remove it on runtime then this is the
Syntax orientation is called excel hidden and then this one is to add um and uh you know like to the to the values okay so again instead of that static name i'm gonna refer to our this uh pivot table variable object and so here it create um here it's adding uh it to the column to the page to the.Row and then this also is going to page and here it's adding it to the values and this one is doing the calculated column and if you want to add the calculated column to the pivot table then you can use this line else you can just comment it out okay so basically this is how it looks like um after you created so you can use.
Something like this to create pivot table on flight we have it on the fly and then there are so many other useful uh properties that you can use because pivot table is used to summarize aggregate and so on so um you might want to dynamically refer to this range yeah so let's completely run it let's call the new sheet as dot name equal to vba.
Period something like that okay so it created this pivot table and then it has all the data points here um the departments the category and items so whatever you add first is um coming at the bottom so you can take care of that by moving this up or you can use the position for that purpose okay um so that is how to dynam you know like automatically run and create a pivot.
Table on the fly um now let's say that we want to start referring to this pivot table and uh maybe look through the fields want to refer to the range want to see the memory usage and so on we can uh try those out some of them now so for example if you have to look through all the pivot tables within the worksheet within the workbook and refresh all of them we can try that now.
Let's call this refresh all pivot tables and now we will say democracy this and then for each worksheet in a current workbook active workbook dot worksheets okay and then from here we want to look through all pivot tables so i'll call this pivot.
Table as this same variable name again pivot table and then for each pd in ws dot pivot tables and here you can simply do this uh dot pivot cache refresh okay so this code is basically gonna look through all the worksheets in the active workbook and then within that particular sheet.
It's going to look through all the pivot tables and refresh it or maybe we can also do worksheet.activate and let me try and run this okay so it has gone through all the sheets and refreshed it okay even if we don't activate also it is fine okay so that is one way to refresh all the pivot tables by looping through or you can also simply uh call the code um.
In case you need it uh dot activeworkbook. oh this is also fine this will refresh all the you know pivot tables uh connections here um this one is gonna be more useful if you want to put in some criteria here based on pivot table names or which you can by the way access using um pivot table name so if you want to put a condition to refresh only specific pivot tables.
Which you can get the name from here you can rename it as well okay now moving on to the next part um let's say that we want to refer to specific area okay this one looks a bit weird let me just delete this pivot table let's use this pivot table for other demonstration.
Okay so let's say that from this pivot table you want to pick up you know i find the range where the pivot table pivot table is located you want to copy the range you want to copy the the data body and so on then there are ways to do it um so i'll quickly introduce you to that let's call this pivot info or you know like uh properties something like that um.
So let me get some of the variable names from here active sheet dot um pivot table the first pivot table okay um and then we want to clear the memory once it's done similarly do it up here as well um.
Pivot cache pivot table and other things okay now coming down here for example um first let's say that we want to get the memory consumption or pivot table dot pivot cache dot memory use okay so this will give us the memory used in bytes okay which you can divide it by 1 0 to 4 or something like.
That to convert it to kb okay and then further more you can divide it again by to make it to mb and so on okay um the other thing it will be various uh is very useful when you have to identify if there are you know like pivot table created within uh the same workbook multiple pivots it is possible that it's referring to different pivot cache index so if you have to change all the index uh you might need to know what is the pivot index for example so it is one uh for example if i recreate a.
Pivot table with the same data set or even a lesser uh something like that um it treats that particular you know uh pivot table as in a different cache so as you can see here is two so even if this pointing to the same data set it's gonna increase a lot of memory usage in your file for example so uh it matters okay so just uh make use of that property to get the index so if you look through here you can simply.
Debug and print this information for example instead of printing i mean refreshing you can also debug the information yeah actually there's only one pivot so that's why it's only returning that and um you can also find out when it was last refreshed um um so for that you can get pivot cache dot refresh name and then.
Dot last no refresh date something like that maybe need to be in the right worksheet okay so here who refreshed it um and then when it was refreshed the name of the user and then the time when it was last refreshed so that also could be useful we already talked about getting the pivot name.
Dot name pivot table one you can also assign a name of pivot name is equal to sample pivot okay um so that's quickly on the getting the information now let's say that we want to read uh the range of where the pivot table is located let's do some examples.
Okay so there is a method or a property called table range one two so let's take a quick look at that um dot table range one dot select and let me do this as well table 2. so if you look here you'll be able to this is very useful so range one is all this range including the you know grand total and.
Everything and then range two will be entire including the filters yeah and then going next so now let's say that um you only want to select the body range then there is a property for that as well data body range so let me run till here f8 now it will only select the data range.
Including the totals and the grand totals oh maybe we can remove this yeah uh now let's say that you want to select um for example uh you want to resize the body range for example if i do this isolating including the grand total so let's say we want to move up um this is more like manipulation of range now so let's see that uh with.
the good thing about just before proceeding further is like irrespective of where you move the pivot table this will always work so that that is the good part about if you have multiple pivot tables you know like uh finding the last row finding the last column and all might not be useful it might not be 100 accurate so it's better to use this method and properties which are available directly for pivot tables um okay so let's say that we want to resize the range.
With this uh range we want to resize so resize and here you want to specify oh sorry let's put this in this rows don't count and then we want to up you know like do a minus one so that and then maybe one just.
Uh dot select let's do that yeah so it's selected less of that and here you can also do um if you already know the size of a pivot table you can simply specify that or you can also do this yeah it will select the entire right now let's say that uh you want to move and include the selection of this.
As well then you can first offset offset maybe same row and then minus one column first and then we want to resize after that okay um so we want to resize this one and then this so let's try this okay so this is how you can refer to the data points uh if you want to you know if someone.
Wants to see the grand total as well but you only have to refer to these screens so it might be useful um referring to you know like pivot tables um sometimes we don't have uh control over who is sending out the data to us you have to deal with all sorts of data so from that angle this uh sort of sort of manipulation can be useful um so you use this columns.com or rails.com to.
Manipulate uh where the pivot is located and then you can play around with that you can also definitely control more other properties for example um okay let me not replicate this range we'll just type it in here in the same pivot table so for example um let's say uh pivot fails something like that um before we go on to filtering out the data set uh let me give a small demonstration of how to change the.
You know like uh the function or the number format of the pivot field so for example let's say that you you have created a pivot table um you must be knowing this but for example if any of the data set within here is text for example like this when you create a pivot table out of this and put um try to put in that into some uh you know like values it automatically goes here because some of them are in you know like not a proper uh.
A number format so it consider that to be attacked so sometimes it is very useful um to have to write a utility where you know like the code will automatically summit instead of count so if you have more data points you can select the fields like this and then you can create a like a utility or a button to convert that automatically to a sum instead of account so for that sort of thing um let me quickly demonstrate it here uh you can use this feature called.