How to create a dynamic pivot table and refresh automatically
Table of Contents
1. How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete data in your data set the pivot is instantly refreshed.
There is no need to manually refresh the pivot table or changing cell references if the data source table grows or shrinks. The Excel defined table uses structured cell references that adjust automatically.
How to create an Excel defined table
- Select a cell in your data set.
- Press with left mouse button on "Insert" tab on the ribbon.
- Press with left mouse button on table button.
- Press with left mouse button on "My table has headers" if needed.
- Press with left mouse button on OK button
Adding new records to the table is easy, simply type on the row right below the last record and the table will expand automatically.
You can also press with right mouse button on on a cell in the table to open a context menu, press with left mouse button on "Insert" and then "Table Rows Above".
Recommended article:
Recommended articles
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
How to auto-refresh pivot table
VBA offers a solution how to automatically refresh pivot table every time you activate "pivot table" sheet, there are other ways to solve this as well like refreshing pivot table every time a cell in data source table is edited.
Press with right mouse button on on the sheet name where you placed the pivot table.
- Press with left mouse button on "View code".
- Paste code to module.
- Change sheet name and pivot table name in VBA code, you probably have a different sheet name and pivot table name.
VBA code
Private Sub Worksheet_Activate()
Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub
Excel 2003 and earlier versions: Create a dynamic named range
This also works in Excel 2007/2010 but it is easier to create a table.
- Press with left mouse button on "Name Manager"
- Create a new named range, named Rng.
- Type in "Refers to:" window:
Press with left mouse button on Close button!
Recommended article:
Recommended articles
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
2. Auto refresh a pivot table
In a previous post:Â How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is edited/added or deleted on another worksheet.
The issue here is that a pivot table doesn't know if
- source data has changed
- source cell reference has changed
That is why you have to manually change the source cell reference and refresh pivot table, this is very easy to forget. However, this article solves your problems using an excel defined table and three lines of event code.
In this example there are two worksheets, "Data" and "Pivot table", to make it simple. The following image shows you some of the data on worksheet "Data"
Watch a video
Create a dynamic source range
Excel is not smart enough to know when you have added values to your data, the source cell reference won't adjust automatically. However Excel defined tables (Excel 2007 and later versions) has that feature and luckily it is easy to implement.
You will also need event code to refresh your pivot table but first let's build an Excel defined table.
Excel 2007 and later versions
- Go to your "Data" worksheet
- Select a random cell in your data
- Go to tab "Insert" on the ribbon
- Press with left mouse button on "Table" button (Ctrl + T)
- Press with left mouse button on OK button
Here is the excel defined table:
Recommended article:
Recommended articles
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
Excel 2003 and earlier versions
The named range formula below is dynamic meaning it automatically expands both horizontally and vertically while adding new entries or columns.
- Start Name Manager.
- Press with left mouse button on "New..."
- Type in "referer to:" field.
=Data!$B$2:INDEX(Data!$2:$65536, COUNTA(Data!$B:$B), COUNTA(Data!$2:$2)+1) - Press with left mouse button on OK.
Recommended articles
Gets a value in a specific cell range based on a row and column number.
Create pivot table
If you already have a pivot table, skip these steps.
- Press with left mouse button on "Pivot table"
- Excel 2007/2010: Type table name in "Table/Range" field
Excel 2003 and earlier versions: Type Rng in "Table/Range" field - Press with left mouse button on OK
Recommended article:
Recommended articles
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
How to auto-refresh pivot table
The following event-code must be placed in the same sheet as your data is. Whenever you edit/add/delete data, event code is rund.
The downside is that it refreshes the pivot table every time you add data to a single cell, this may slow down your workbook on an older computer.
Read this post if you'd rather refresh your pivot table when you activate pivot table worksheet.
Here is instructions on how to insert the event code to your "pivot table data source" worksheet:
- Press with left mouse button on "Developer" tab on the ribbon.
- Press with left mouse button on "Visual Basic" button
- Double press with left mouse button on "Data" sheet in project explorer window.
- Insert event code into worksheet code window to the right of the project explorer window.
VBA code
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
The code is copied into the code window for sheet "Data". The Worksheet_Change sub is rund when a cell is changed in sheet (Data).
This line of code refreshes the pivot table on sheet Pivot table whenever a cell is changed on sheet Data.
Press with mouse on any cell in your pivot table and go to tab "Options" to see the name of your pivot table. Make sure to use that name in code above.
Your pivot table is now instantly refreshed whenever a cell value on worksheet "Data" has changed, and the pivot table source data range is adjusted if values have been added/deleted to your excel defined table.
Get Excel *.xlsm file
Pivot table category
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Excel categories
112 Responses to “How to create a dynamic pivot table and refresh automatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Moment you use tables you are restricting it to 2007/2010
Instead create a 2d dynamic name
Data
='1'!$A$1:INDEX('1'!$1:$65536,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1)) and use it in the Pivot Range
Great comment, thanks!
hi oscar,
thanks for this cool tip!
btw, if i have another Pivottable (using different pivotcache), can i add an additional line?
e.g. PivotTable2 is using diffent pivotcache than PivotTable1. If you're wondering, i use different pivotcache due to date grouping (PivotTable1 = daily, PivotTable2 = 7-days grouped)
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Pivot table").PivotTables("PivotTable2").PivotCache.Refresh
End Sub
thanks!
davidlim,
Yes, that works. Make sure you have the correct sheet and pivot table names.
Thanks for sharing!
HI Im confused on how we do this in excel 2013
hi oscar,
i noticed there is a problem on pivottable with grouped dates.
my Data sheet is an Excel 2010 Table. Data is daily stats, therefore i add new daily data below the Table.
whenever new data is appended to the Table (new daily data), the PivotTable will automatically "ungrouped" (grouped Dates --> single Dates).
I had to manually regroup the dates again.
have you encountered this?
davidlim,
It seems that the pivot table preserves formatting if you copy and paste data to your table or dynamic range. When you move a cell range to the last row of a table or dynamic range, pivot table formatting is lost.
Try copy / paste next time you append new data to your table.
hi oscar,
thanks for the response.
fyi, i performed copy-n-paste "value only" when i append the data.
also, i've tried another macro: refresh pivottable when pivottable sheet is selected. <-- this doesnt have any issue :)
anyways, this alternate macro suits me as well.
very much appreciate your help!
Hi,
When I try this with Excel for Mac 2011, the sheet appears to get stuck in a loop where it's recalculating the sheet over and over again. The only way to get out is to force quit Excel.
Ideas?
Hi Oscar,
I was able to get this to work on Excel 2011 (Mac) but when I open up the same file in Excel 2010 (PC) the macros don't run. I've enabled Macros and set the security settings to run all Macros.
Essentially what I have is multiple pivot tables across 6 worksheets all using the Data worksheet as the data source.
I tried to update the Macro to use the code you mention in the post "https://www.get-digital-help.com/2011/06/13/auto-refresh-a-pivot-table-in-excel/". Using this the macros runs but I get a run-time error '1004': Application-defined or object defined error.
Any thoughts on what I can do in Excel 2010 to get my macro to run?
Thanks - Tim
I'm not sure that I'm in the right location for this, and I'm working handicapped with v2003 and I don;t know vba.
I have an xml file thats exported from a third party tool. I import that into excel and add a column to the far left. In this column I do a match fucntion. This function matches specific data in one column of the xml data to "sheet2" which containes a list of codes that I care about. Then I create a pivot table that uses the match (1-30), ingnores blanks and NAs to filter my pivot. What gets displayed is all the information I care about. Works great. Problem is I'm doing this on a weekly basis and realize there must be a better way to link to the xml, still make my match to my codes and create a piviot table.
Question is how to acheive this. No luck treating the xml as a database. Excel is not seeing it.
Please advise and thanks for the expertise.
RE,
Is it possible in excel 2003 to import xml using Get external data, from other sources?
Import multiple XML data files as external data
If it is then you should be able to refresh external data source if the xml file name is the same.
There is a much much easier way to keep the pivot table dynamic guys!!
Simply reference only the top row of your data. Leave out the column references.
Example- Before: $A$1:$Z$5000
Example- After: $A:$Z
Notice we are completeley leaving the numbers out...works like magic!
JamesO,
Yes, it works but only when the data begins on row 1.
I have not tried this in excel 2003 but if it works you can skip the "named range" part.
You still have to press with right mouse button on your pivot table and select "refresh" to refresh pivot table.
Thank you for commenting!
Hi oscar,
Thank you for very helpfull function. Please share how to save. Because, After create this , save and close this excel, and I open this excel file, but this VB didn't exist.
Please inform me how to save.
Thanks a lot
luluk tri,
Save as an *.xlsm file
Hi Oscar,
Thank you for your advice. Once more, please assist me.
All of macro was run smoothly. But, I found that "UNDO" was deactivated by system. How to activated "UNDO".
Thanks.
luluk_tri
luluk_tri,
Yes, the undo stack is lost.
Undoing A VBA Subroutine
Hi,
I was wondering if there is a way to refresh when the data that is being changed is in a controlbox. I'm using active x controlboxes to select from a list which in turn will adjust prices and so forth. If I change a selection in the controlbox the pivot table is not updated. The script is working if I change a data entry manually and hit enter to tab to move to a new cell.
Thanks,
Ernie
Ernie,
Press with right mouse button on sheet name. Press with left mouse button on "View code". Change combobox name. Copy/Paste code.
Private Sub ComboBox1_Change() Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh End SubSimple, straightforward information. Thanks very much.
I am getting run-time error '1004'
whenever i update the Data sheet.
G,
Did you change the names (bolded)?
Pivot table <-- Sheet name PivotTable1 <-- Pivot table name
Great tip. However taking it a step further, can the pivot table only update when 1 cell is changed, and not any cell on the tab.
Example say I only want the table to refresh when cell AB4 is changed.
How would you do that?
Joe,
Private Sub Worksheet_Change(ByVal Target As Range) If target.address = "$AB$4" then Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh End If End Subcan you please explain the function of cache here
Macro works but then it is preventing undo..any workaround for that?
Sachin,
it is possible to undo a macro but I am not sure about the undo stack:
https://www.j-walk.com/ss/excel/tips/tip23.htm
https://www.jkp-ads.com/Articles/UndoWithVBA00.asp
after applying the method the "undo" command doesn't work..any solution??
I can't seem to ge tthis to work when the data and pivot table are on the same sheet. Is tha possible
Does not seems to work with pivot table in the same sheet here either... any suggestions? :)
Hi,
I want to auto update the pivot table.The issue is I'm not running vb in excel .I have update the pivot table with my third party software vb tool.I have to define my pivot table folder path and also i need to refresh the pivot table
Filipe,
Make sure you have changed the sheet name and the pivot name (bolded):
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
PMS,
I have no clue.
Oscar,
Thank you so much for your help.
I have specified the correct workbook and piveot table, and, for a short time, I can see the values to change... The problem is that this seems to make excel to run an infinite refreshing loop which causes excel to crash.
Any sugestions?
Have a great day,
Filipe,
If pivot table and source data are located on the same sheet excel goes into an infinite loop.
This code takes care of that problem:
Private Sub Worksheet_Change(ByVal Target As Range) Dim pt As PivotTable Set pt = Worksheets("Pivot table").PivotTables("PivotTable1") If Intersect(Target, pt.TableRange1) Is Nothing Then Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh End If End SubGet the Excel *.xls file
auto-refresh-pivot-table1.xls
Oscar
I have an (Excel 2010) expense Claim report design which has the Expense Data (Table2) on the same sheet (Expense Claim) as a pivot table (pivottable1)which summarises the claims by expense type. Your suggested macro above works nicely to auto refresh the pivot anytime I add in an extra expense claim item, but to allow others in the company to use it I need to protect areas of the claim form - otherwise they mess up. The Auto Refresh macro doesnt work when I protect the sheet. Is here a few lines I can insert into the Macro to fix this?
I'm having the same problem. after update 1 cell the pivot table refreshes but i get the message: Run-Time Error -2147417848 (80010108) Method 'Refresh' of object PivotCache Failed.
Joao, read my answer to Filipe.
Hi Oscar,
That is brilliant, thank you so much.
The specific workbook I have happens to have two pivot tables populated from the same table source, and in that case I could not tweak your code to make this possible.. it keeps refreshing for long time.. With only one pivot table works really great.
Suggestions are very welcome,
Filipe,
Private Sub Worksheet_Change(ByVal Target As Range) Dim pt1 As PivotTable, pt2 As PivotTable Set pt1 = Worksheets("Pivot table").PivotTables("PivotTable1") Set pt2 = Worksheets("Pivot table").PivotTables("PivotTable2") If Intersect(Target, pt1.TableRange1) Is Nothing And Intersect(Target, pt2.TableRange1) Is Nothing Then Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh End If End SubThanks Oscar for the solution,
would you mind explaining why there will be an infinite loop when the pivot tables and source are located in the same worksheet?
Oh, I seem to get it.
Is that because after refreshing, another change event is triggered?
Sam,
Yes, when the pivot table refreshes the change event is launched again.
Great, great, great, thank you so much, this is great!
Hi Oscar,
Thank you for the post. This is great!
I come an issue when using this though. I have a workbook with 2 sheets and derive 5 pivot tables from each sheet (10 pivot tables). I tried the code with 5 lines (1 line for each pivot table) in each sheet initially, then changed it to a single line for each sheet. But still excel continuously executes the query and doesn't stop. I need to press 'Esc' and 'Debug' in order to stop it.
Any suggestions?
Looks like Excel has a single Pivot cache for all 5 charts from a single sheet. Thus I used a single line of code to refresh the pivot cache. Can you please confirm if this is correct?
I think the continuous execution of the query has stopped for some reason and it works fine now. Thank you very much.
Hi Oscar,
Thanks for your postings, I hv used this short sub-routine and it works.
My problem starts only when i applied a sort order (in Descending Order) on the first column of my pivot table that contains a date. Everytime that a new data is added on the source sheet, my sort order in the pivot table scrambled and out of order.
My question to you now is - How to keep the sort order intact when there is/are data change? Can you add on above sub routine code something that when data refreshes, the sort order refreshes also to DESC order?
Guru,
My problem starts only when i applied a sort order (in Descending Order) on the first column of my pivot table that contains a date. Everytime that a new data is added on the source sheet, my sort order in the pivot table scrambled and out of order.
I tried adding dates in excel 2010 the dates are not out of order. But this macro should do it:
Private Sub Worksheet_Change(ByVal Target As Range) With Worksheets("Pivot table").PivotTables("PivotTable1") .PivotCache.Refresh .PivotFields("Date").AutoSort _ xlDescending, "Date" End With End SubOscar,
Thanks a lot for all the useful information you provide here. I opened your example file and it worked, although it says on the top it runs in [Compatibility Mode].
Now I don't manage to get the code executed in my orginal file. I even copied your worksheets into my Excel file, and then the automatic update stops to work again. Am I right assuming that nothing with the code is wrong, but some settings in my Excel prevent it from running the code?
Any help where I could look is highly appreciated!
Sven,
did you check the sheet and pivot name? I assume you have other names?
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
Hi Oscar, yes I did. I also copied the tabs from your example workbook into my file, they stopped to work too. So must be a problem with my workbook settings? But it is a macro enabled xlsm-file...? I am obviously clueless! :)
Hi Oscar!
This is really helpful. I have one question though. How do you tweak the codes when the source data is stored in a separate workbook?
Thanks!
bert,
Workbooks("Book1.xlsx").Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.RefreshHi Oscar!
Thanks a lot for your help! I already got this to work in some of my routine jobs! I'm searching your site for other things that can help me more. Great job for you!
Hi Oscar,
Good day
Hi Oscar
You little genius you. Thank you so much, exactly what I wanted (Mac 11 User)
Hello,
Have a pvot table for monthly reporting of #sales made by each sales paerson. Data is generated from a SQL quarry, capture the data and recreat the the table of report by pasting is to Excel. This done every month, I wonder if this table can be made dinamic so that it can pull the data each month from the quarry?
Regards,
G
Hi dear,
How to Number form Sentencce which in celll
Example:- In cell a sentacne
Problem Summary(Cell 1)
spk to mr r k chabra call 4 want to shift the conection in same room alt no is 91-9818786350 plz chk dn do need full asap.............
REQUEST RELATEDTariff MigrationContact Person:tarun Contact Number:9871431939Authentication Done:YesCheck OG Status - activeSegment Check:YesCurrent Plan :Airtel - UL - Smart One Combo 8GB 799(2Mbps/256Kbps) DSL Plan (Subscription
Disturbance-All Times (Cell 5)
Ateeq
91-9891608537
I need a little help on how to automate grouping (categories are: 1-30, 31-60, 61-90, 91-120, 121-180, 181-210, 211-240, 241+) in a pivot table and create a macro to automatically update them each week from data source, and will populate pivot totals in another tab. I have 6 weekly reports that I have to create about 20-25 pivot tables for each, each week.
I can send a copy of the file, if you would like to take a look at it.
Can you help me please?
[...] Have a look at the following... How to create a dynamic pivot table and refresh automatically in excel | Get Digital Help - Microsof... [...]
Thank you so much you are a lifesaver!
The code works while i am in the sheet however once I save, exit, then reopen the sheet - the refresh no longer works... I am unsure what is going on.
Can you help?
Torri,
Save the file as a *.xlsm file.
Hi Oscar,
I have the following issues. Any suggestions.
Sheet 1 :
Grade Male Female
Primary
Secondary
High School
College
Gender
Sheet 2 :
Col A : Student Id
Col B: Gender : Here we specify whether the student is male or female
Col C : Here we specify category to which the student falls : Primary, secondary, high school, college
Col D : Age : To which age they are in
Problem :
In Sheet 1,
# I should give the count of students who are male and are in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years ...
# I should give the count of students who are female and in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years
Kindly let me know of any specific formula e that I can use.
Kiishore,
See this post:
https://www.get-digital-help.com/2013/05/16/count-students/
Thanks so much. It did not work the first few times I saved it that way ... but the third time was the charm and it stuck. Thanks so much.
such a helpful thread.
i used "OFFSET" instead. much easier and dynamic!
Ali Hasnain,
How did you get the pivot table to update automatically?
How can it be easier than an excel defined table?
Thank you.. it's very helpful for me.
Thanks very much! very helpful!!!
Torri, Amit and Luch
Thanks for commenting!
Works great! I have two questions though:
1. Every time I change data in the 'data' sheet, excel demands confirmation to replace the contents of the destination cell in the sheet containing the pivot tables. Any way to bypass this? I noticed your example does not seek confirmation, so I know it's possible.
2. Is there any way to protect the sheet with the pivot tables from end users manually changing data within the pivot tables and still allow them to be refreshed?
Thanks in advance!
The Data I am pulling from is:
ATA Claim Amount # Units
11/7/2010 $322.89 4
3/29/2011 $41.36 24 (4 ctns)
3/4/2011 $20.68 12 (2 ctns)
3/24/2011 $72.38 42 (7 ctns)
7/29/2011 $50.79 2
8/4/2011 $101.58 4
8/5/2011 $253.96 10 (2 ctns)
12/9/2011 $76.19 3 units
12/15/2011 $78.36 4 units
9/13/2011 $54.42 2
12/13/2011 $612.23 25
10/27/2011 $24.49 1
3/10/2012 $24.49 1
4/18/2012 $489.78 20 (4 ctns)
4/16/2012 $124.08 72 (12 ctns)
5/8/2012 $1,224.45 50 (10 ctns)
7/1/2012 $59.86 1
7/15/2012 $489.78 20 (4 ctns)
7/17/2012 $122.45 5 (1 ctn)
11/10/2012 $119.72 2 (1ctn)
11/18/2012 $199.54 10 (2 ctns)
10/29/2012 $748.82 192 (32 ctn)
12/6/2012 $25.58 1
12/9/2012 $25.58 6
12/3/2012 $126.98 5
11/4/2012 $38.09 3
7/31/2012 $122.45 5
2/17/2013 $54.42 5
10/26/2012 $20.68 12
3/23/2013 $982.83 252
4/8/2013 $51.15 12
4/3/2013 $39.91 2
2/25/2013 $54.42 5
3/27/2013 $23.40 6
5/20/2013 $25.58 6
5/7/2013 $49.89 5
6/10/2013 $25.58 6
This was my instructions:
I need them placed into graphs showing the time line from 2010 to present.
We need to measure three areas on a monthly bases; frequency of events, total amount of claim for the month and unit count for the moth.
I need it in a three bar graph and line chart with three spate lines for each measurement.
Hi Oscar,
I am looking for a code to refresh my data from ODBC on the first day of the month by 4 a.m. without having to be on the computer to open the excel file.
I have VBA code to refresh Pivot Table that works wonderfully (except loosing some center text and a few boundary lines.) Now I would like to insert code to Refresh the sheet when it is protected via a macro button placed on the protected pivot table sheet. The data source is sheet 2 (named 'PRev') and the pivot table (named 'PivotTable1A')is on sheet 1 (named 'Scott PT'.
The code I have for refresh on 'Scott PT' sheet in module1 is as follows and a suggested code for refreshing while protected sheet is on the pivot table sheet 'Scott PR', that I don't understand how to incorporate at the same time,follows after the break after this first code:
___________________________
Code installed that works for refresh follows:
____________________________
Option Explicit
------------
Sub ClassicPlusPivotTableSettings()
'Last modified and tested 2010-07
'Much of this is based on a book by Debra Dalgleish
' named "Excel Pivot Tables Recipe Book: A Problem-Solution Approach", Chapter 13
'
'The following code applies to PivotTable(1) on the active sheet.
'In summary, this macro:
' Applies the Classic PivotTable display, with gridlines and no colors
' Ensures that only data that still exists in the data that drives the PivotTable
' will appear in the PivotTable dropdown lists
' Sets all fields to ascending order with no subtotals
' including fields that are not in the Row Labels or Column Labels areas
' For the data field(s) in the Values area, changes the setting to Sum, changes the number format, and
' if the field in the Values area is named "Amount" ot "Total Amount" it shortens the label
' in the PivotTable to "Sum Amt" or "Sum TtlAmt" respectively.
On Error Resume Next
Application.ScreenUpdating = False
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
'This section applies Classic PivotTable settings
' and turns off the Contextual Tooltips and the Expand/Collapse buttons
With pt
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.TableStyle2 = ""
.DisplayContextTooltips = False
.ShowDrillIndicators = False
End With
'This sets each field in ascending order. It applies this even to fields that are not currently
' in the PivotTable.
For Each pf In pt.PivotFields
pf.AutoSort xlAscending, pf.Name
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
'This command changes the formatting of any field that appears in the Values area
For Each pf In pt.DataFields
pf.Function = xlSum
pf.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next pf
'These next two sets of statements shorten the description of any field in the Values area,
' but only if the field is named "Amount" or "Total Amount"
With pt.PivotFields("Sum of Amount")
.Caption = "Sum Amt"
End With
With pt.PivotFields("Sum of Total Amount")
.Caption = "Sum TtlAmt"
End With
pt.ManualUpdate = False
Application.ScreenUpdating = True
'This ensures that only data that still exists in the data that drives the PivotTable
' will appear in the PivotTable dropdown lists
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
End Sub
___________________________
Code for refresh while protection is on and for macro button follows:
___________________________
Press with right mouse button on the sheet tab of the sheet containing the pivottable.
Select View Code from the context menu.
Enter or copy/paste the following code into the worksheet module:
Private Sub Worksheet_Activate()
Me.PivotTables(1).RefreshTable
End Sub
Remark: you have to protect the sheet with UserInterfaceOnly:=True each time the workbook is opened:
Double-press with left mouse button on the ThisWorkbook node in the project explorer on the left hand side of the Visual Basic Editor.
Copy/paste the following code into the ThisWorkbook module:
Private Sub Workbook_Open()
With Me.Worksheets("Sheet1")
.AllowPivotTable = True
.Protect Contents:=True, UserInterfaceOnly:=True, _
AllowUsingPivotTables:=True
End With
End Sub
where Sheet1 is the name of the sheet containing the pivottable.
example i have 3 sheet student's attendance.
i use sheet 1 to check 30 student from class A: attend or absent and sheet 2 to check same 30 student but from class B
and i use sheet 3 as a record and make option Class : A or B. If i choose Class A, i can see which student whose absent from Class A only. And if i want choose Class B, THE TABEL WILL AUTOMATIC change to Class B and i can see which student whose absent from Class B
Can u give me what's the tutorial to make that?
can we do the multiple cols. sorting in excel and sample
Kannan,
can you explain in greater detail?
Perhaps you are looking for this post:
Sort a table with an array formula
I added this code to a report that I developed and when other users with Excel 2013 open the file, they get a VBA Runtime error. However, there are two workbooks that it is in and only one of them gives the error. It is acting like the table names or sheet names are not correct but I have double checked everything and also compared the two workbooks. Any thoughts? Would the newer version of excel be causing issues? Thanks for the help and the code!
Jesse,
It is acting like the table names or sheet names are not correct but I have double checked everything and also compared the two workbooks. Any thoughts?
No other than the ones you mentioned.
Would the newer version of excel be causing issues?
I don´t know, sorry.
[…] Auto refresh a pivot table in excel | Get Digital Help - Microsoft Excel resource […]
Hallo Oscar,
thank you for your great post! My problem is actually quite different. I would like indeed to refresh automatically my pivot table whenever the source table is changed. The problem is that my source table ist not directly edited, but is a result of several other tables and computations.
If I use the event "Worksheet_Change" it doesn't really work since Excel doens't trigger the event when the source table is recalculated (other changes are of course correctly handled).
On the other hand, if I use the event "Worksheet_Calculate" then I can handle the situation but every calculation on the entire sheet will trigger the event and hence the pivot table refresh.
Is there a way to combine both things, that is, that the pivot table is automatically refreshed when the source table is either changed or recalculated?
Thank you very much in advanced!
Jose
Oscar EXCELLENT help for VBA simple routine on AUTO Pivot Table update.
Hello Oscar,
I have a pivot table sourcing data from another pivot table (on the same worksheet); can I get the second pivot table to automatically refresh when the 'master' pivot table is updated?
Thanks,
Alan.
Oscar, you sir are a steely-eyed missle man!
Thanks for the great info.
Working fine mate....All the pivottable charts are automatically updated too
Hi
Oscar
I do the same exercise as you explain above but when i close and again open the workbook its not working.
Please help
Hi,
Whenever I refresh a data, the numbers changes its format to dates. but from my source data, the data is still in numbers..
Hi Oscar,
The basic code works for me for refreshing, but i then lose the cell border/highlight when moving around the workbook - ie. i cant see which cell i'm currently in.... any ideas on that one?
Thanks
Chris
Thanks it worked
Thank you so much for your help. That was very helpful !
Hi Oscar,
the post is really informative and helpful.
I have multiple pivote tables and multiple source data sheets aswell..
for example in workbook 1 , i have pivot1 which is generated from the data of sheet1 and pivot2 from sheet2...so on.
I can write code in specific sheets to refresh respective pivot tables but How can i keep the code in single macro? so that it would be easy to make changes or debug if required.
thanks in advance
Hello Oscar, this is very cool, however I have an added complication, I'm hoping you can help me with. I have other sheets feeding into my data sheet which then feed into the Pivot table. So when a change is made in the data sheet it is via a link not an actual user action in the cell. This results in the pivot table not refreshing. Do you have any suggestions of how to make this work. Otherwise the pivot tables have to be refreshed numerous times to update all of the data.
Thanks
Simply use vloopup like
IF($B$1="A",VLOOKUP(Sheet3!A2,A!$A:$B,2,0),VLOOKUP(Sheet3!A2,B!A:B,2,0))
I have a template that I have created to process phone bills. There are may lines of GL coding for particular individuals. The amounts are different every month. I have created a pivot table in the template (both data and pivot table under one tab). I have created a refresh button to refresh the PT. It works in my original tab but when I copy to create a new tab to process a new invoice it does not refresh on the copy. My pivot table is still connected to the original tab. When I enter data in my original tab and refresh on my copy it moves. How do I keep the connection between my source data and pivot table in the copy together and not the original tab?
hi
i am trying this but error cames
Worksheets("PivotTable1").PivotTables("Design").PivotCache.Refresh
subscript out of range
also another error occur i.e. Unable to get the pivotTables property of the worksheet class
Dear sir I have copied the vba code here and it done the magic . Thank u .
But I have 2 more worksheets in my inventory , i am unable to do the same again for the respective pivot tables
Kindly help me in this regard
Thank you
Thank You So Much...
Helped me lot!!!!
Hi,
oscar.
I'm new one in this group and i already read ur post which help me greatly. would u be told me that how to keep equal distance among several columns after refresh. Because when i refresh the pivot sheet then it scrambled how to keep it equal distance between column.
When I try to do Worksheet_Change my code doesn't work and even when I try to just refresh a single sheet it still turns into and error/endless loop. Plz help
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each ws In ActiveWorkbook.Worksheets
Call PivRefresh
Next
End Sub
Sub PivRefresh()
ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh
End Sub
Hi Oscar,
When I use your original VBA on a pivot table with that works from a range that has values only it works automatically. When I try to use in on a range that contains the results of formulas I have to manually refresh. Is there a way to make it work automatically from source data that contains formulas?
Thanks,
MikeS
MikeS
Is there a way to make it work automatically from source data that contains formulas?
This post shows you how to refresh a pivot table when you activate a sheet, is it something that would work?
https://www.get-digital-help.com/2011/04/26/how-to-create-a-dynamic-pivot-table-and-refresh-automatically-in-excel-2007/
If not, your formulas must work with a data from somewhere in your workbook? You could relocate the event code presented in this post to the sheet where your data is located?
What an amazingly simple piece of code that makes rolling out multi-user SQL linked reports by pivot table so easy to administer without further training. Thanks for sharing
Hi, thank you for the trick!
I have multiple Worksheets each one with one or more Pivots. What if I want to apply the above code to all the pivots and worksheets?
I was wondering if there is anything "faster" than repeating the command for each table
Thanks!
Nik
This code refreshes all pivottables on a sheet if a cell value has changed on the same sheet:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim pt As PivotTable Application.EnableEvents = False For Each pt In Sh.PivotTables pt.PivotCache.Refresh Next pt Application.EnableEvents = True End SubMake sure you put the event code above in "ThisWorkbook" module.
Note, this will only work as intended if the source data is on the same sheet as the corresponding pivottable.
Hi Oscar,
Please I am hoping you'll be able to assist me. I designed a template whose data is driven by a Pivot table source. The pivot Table is in turn driven by a source data in the same workbook. I used "IF" formula to pull the data from the pivot table(for example "=if('Sheet1'!B17="","",'Sheet1'!B17). the challenge is that source data are dynamic on a month to month basis and each time I update the the pivot table based on the source data. The 'IF' formula is static and does not change with the template row heading. Please what can i do to make the template(&'IF' formula) row heading to be dynamic with the pivot table when refreshed.
Just to note that I am not that too vb savvy.
Please reply.
Thanks
Hi Oscar,
I am new to Macro. Please let me know the code through which pivots can get refreshed even when data is added/increased. When I remove some data, it gets updated automatically through the above code but once data is increased it does not gets updated because of range issue. Please help.
can I auto-refresh a linked table similar to a pivot table? both the source table and the linked table are in the same worksheet. any solution with or without a macro is ok. If the source table values change either because o manual entry or because of formula change, the linked table also should change automatically, currently, the refresh happens only if I press with the left mouse button on the refresh button in the table tools design tab
[…] this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named […]
May I have 1 question? if the pivot table data is linking to another data. For example in the pivot table data, the amount is "=Sheet2!F1". Even if i update the number in Sheet2 F1, the pivot table cannot refresh. Unless I physically press with left mouse button on sheet 1. Do you have a solution?
Steven
Yes, the event handler Worksheet_Change won't be triggered if a cell is linked to another sheet even if the linked cell value changes.
A simple solution would be to use Worksheet_Change on that worksheet as well.