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
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Excel categories
94 Responses to “Auto refresh a pivot table”
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.
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,
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
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:
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.
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.
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
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
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,
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
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.