Pivot table not updating with new data
Basically, we need the subcategories of the environmental category for our X axis labels, we want the corresponding defect totals for our Y values, and we need the environmental category label for the series name. Sub Update Chart(cht As Chart) Dim r XVals As Range Dim r YVals As Range Dim r Name As Range Dim s Srs Fmla As String Dim PT1 As Pivot Table On Error Go To Leave This Mess ' define chart data ranges Set PT1 = Active Sheet. I’ll use the approach I described in Referencing Pivot Table Ranges in VBA to identify these ranges.
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. Here’s the pivot table from the dynamic chart article. The duplicate columns Main Category and Category were needed in order to have the categories (electrical, mechanical, etc.) in both the row area and column area of the pivot table. The code to select the environmental category label (series name) is Intersect(Active Sheet. Formula = s Srs Fmla End With End With Leave This Mess: End Sub The following routine moves the active cell to the bottom right cell of the sheet, where hopefully there won’t be any pivot tables. Then the sheet is scrolled up to the top left cell. The new cells are now formatted correctly, and the formatting range will automatically expand, if more records are added to the source data.
You can download the Conditional Formatting workbook to see the pivot table used in this example. To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in.
If they are prone to change size there seems little to be gained from having them all in the same worksheet, so hold each in a separate worksheet.
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.
Not bad, but our objective is to show only one category, say, Environmental.
One of the limitations of a pivot chart is that we can’t plot a subset of the data.
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.