It has been very important now-a-days as we handle large size of data and every now and then we are asked to prepare Pivot summary of the data. This become laborious , hectic and repetitive task, also time consuming. To get rid of with this time consuming activity let us learn smart method.
Auto refresh pivot tables in excel by caching the pivot with help of VBA coding. Even you can set the table range in named manager and use as source.
How to create a dynamic method to auto refresh existing pivot tables?
There are two ways that you can work smartly to work on pivot tables faster and make your boss happy with sharing the accurate result in few minutes.
Creating a Data table and using the table name in Source Data of Pivot Table
1. Create a data table
2. Give a sensible name to Pivot table
3. Use the table name in Data Source of Pivot Table
4. finish
Let us try auto refreshing pivot table without VBA Coding,
here is an example
Create Dynamic table, give sensible name to the table and use the table name as source data range, that's it.
On adding the rows data in the existing table, just refresh the pivot table and your pivot table cache will get refreshed with latest values.
Now another method is, write VBA Code which will auto refresh the Pivot with updated data
See example below, you can download the practice workbook with VBA Coding, click on link below.
1. Update the data in tabular format.
2. Create Pivot manually (Ctrl + DP)
3. Prepare the Pivot table
Follow the steps as mentioned in below animated clip.
Once you are done with creating table manually. Now let us start writing macro to auto refresh pivot table by using Pivot table cache refresh property of Pivot table.
Press F11 to open VBA Editor
Give sensible name to your procedure
Define required variables to hold the name of worksheets, range and so on
In this case, variables required as shown in above image
Pt_Sht variable is used to hold the name of Sheet where Pivot table is created
Dt_Sht is declared explicitly to hold the name of data sheet
as shown below set the names of worksheets.
lr variable will hold the number of used row
rng variable will hold the set of rows defined in range property
as shown below pass the number using cells property and range as range
PtName variable will hold the name of Pivot table which we already had given to pivot table
Please view the above animated clip to learn how to assign the name to pivot
NewRng as string, simply write the code as shown below, this will be referred as Source data in coding
This feature of assigning data range to cache to named pivot
This is the final step to refresh the cache and your pivot gets refreshed automatically once you execute the code.
Download link to Google Drive << VBA Refresh Pivot >>
No comments:
Post a Comment