Updating pivot table data
It’s the same data used in the dynamic chart article cited above. Here’s the pivot table from the dynamic chart article. How about some code that makes a chart based on only part of a pivot table, and updates it on demand? Address(, , , True) & ",1)" With cht ' clear out chart data Do If . Sub Create Chart From Partial Pivot Table() Dim cht As Chart Application. The data we’re using shows causes of failure in a system, broken down by categories and types of failure. Data Range) ' write series formula s Srs Fmla = "=series(" & r Name. Screen Updating = False On Error Go To Bail Out ' make sure active cell isn't in pivot table ' if it were, the chart would be a pivot chart With Active Sheet . For example, the selected range has to be nowhere near the pivot table when we insert the chart. I’ll write a routine that makes sure that the selected range doesn’t overlap with a pivot table and insert a new chart, then call the first routine to populate the chart. Then we need to add the chart data one series at a time. Finally I’ll write a short routine that calls the update chart routine, specifying the active chart.
Dynamic range can expand automatically whenever you add new data into your source sheet. Now, you have a dynamic range to create a pivot table. Activate Msg Box "Your Pivot Table is now updated." End Sub Data_Sheet. Just choose one of the above-mentioned methods, put your data on your data sheet and refresh your pivot table.
In Referencing Pivot Table Ranges in VBA I showed how to identify certain sections of a pivot table using VBA. Delete Loop ' add brand new series with data we've defined With . It disables screen updating while it does this, so the user doesn’t get a case of vertigo.
In Dynamic Chart using Pivot Table and VBA I showed how to update a non-pivot chart when a pivot table updates. The routine inserts a chart in the default position (centered in the window), makes it a clustered column chart, then sends the chart to the routine above to be populated for the first time.
Screen Updating = True End Sub Fairly elegant for something so quick and dirty. I’ve shown it next to the pivot table with the plotted series selected, so you can see the highlighted ranges used to plot the data.
, author Curt Frye provides comprehensive, hands-on tutorials on Excel Pivot Tables, including more advanced techniques such as using macros and the new Power Pivot add-in.