Auto update a pivot table

[tmckerahan]

Hi Chandoo, I just discovered your site earlier this week and am already a huge fan. Thanks for putting all this together...and, most importantly, in a clear and entertaining way!


My question is regarding pivot tables. I have a pivot table pulling data from a data table, which works great, except the pivot table does not update automatically when you add or edit data in the data table. If this worksheet was for my own uses this would be fine because I would know to refresh the pivot table, but this worksheet will be going to less-Excel-savvy colleagues who may not remember to do so, so I'd like to automate the updating if possible. Can you recommend any techniques to make this automatic?


Thanks so much!

Tiffany

 

[Hui]

tmckerahan

The easiest way is a quick macro which has to go on the worksheet code page for the page where the data table is


Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh

End Sub


What this does is

When anything on the Worksheet, where you have your data, changes

The macro will run

and it tells the Pivot table "PivotTable1" on "Sheet4" to refresh

Obviously change the PivotTable name and Sheet name to suit


This can easily be extended to only execute if a certain range changes

ie: Columns 4 or 5 and Row <10


Private Sub Worksheet_Change(ByVal Target As Range)


If (Target.Column = 4 Or Target.Column = 5) And Target.Row < 10 Then

Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh

End If


End Sub

 

[Karlin]

Hi Hui,

I am very new to VBA code. I have created 2 very similar dashboards where only the data is different, otherwise, the dashboards look exactly the same (the Pivot Table names are different though). I used your code in one dashboard so pivot tables would automatically update when the data in a table refreshed, and it worked great. However, when I opened the second dashboard without any VBA code while the first one was still opened, I got the following error:

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

When I click the Debug button, I see the entire third line highlighted in yellow (I put the font in blue bold in the example below):

******************************************
Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("Sheet 1").PivotTables("PivotTable5").PivotCache.Refresh
Worksheets("Sheet 2").PivotTables("PivotTable6").PivotCache.Refresh
Worksheets("Sheet 3").PivotTables("PivotTable7").PivotCache.Refresh

End Sub
*****************************************

Is there some way to associate the code to a particular dashboard so I can have multiple dashboards open without any problems? Or there might be a different way to remedy the problem?