Auto refresh a pivot table in excel
In a previous post: How to create a dynamic pivot table and refresh automatically in excel 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 removed from a sheet.
In this example there are two sheets.
- Data
- Pivot table
Create a dynamic named range
Excel 2007/2010 users can create a table. It is dynamic by default.
- Select "Data" sheet
- Select cell range
- Click "Insert" tab on the ribbon.
- Click "Table" button (Ctrl + T)
- Click OK.
Excel 2003
- Start Name Manager.
- Click "New..."
- Type in "referes to:" field.
- Click OK.
Create pivot table
- Click "Pivot table"
- Excel 2007/2010: Type table name in "Table/Range" field
Excel 2003 and earlier versions: Type Rng in "Table/Range" field - Click OK
How to auto-refresh pivot table
- Click "Developer" tab on the ribbon.
- Click "Visual Basic" button
- Double click on "Data" sheet in project explorer window.
- Paste vba code into worksheet code page to the right of the project explorer window.
VBA
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
End SubThe code is copied into the code window for sheet "Data. The Worksheet_Change sub is executed when any 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.
Pivot table is instantly refreshed whenever a cell is changed.
Download example file
auto refresh pivot table.xls
(Excel 97-2003 Workbook *.xls)
Related posts:
How to create a dynamic pivot table and refresh automatically in excel
Follow hyperlinks in a pivot table
Change pivot table data source using a drop down list
Recommended categories
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 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,
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,
Right click on sheet name. Click "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 SubMacro 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:
http://www.j-walk.com/ss/excel/tips/tip23.htm
http://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 SubDownload excel *.xls file
auto-refresh-pivot-table1.xls
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 downloaded 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
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
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?
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:
http://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.
Thank you.. it's very helpful for me.