I’ve done this in a number of projects, so I thought I’d illustrate it with a simple example. Let’s start with this dummy data in a list (I’m using Excel 2003). Drag the Item field to the Rows area, the Category field to the Columns area, and the Value field to the Data area. Now create a regular chart from this pivot table, using series in columns, one series per item in the Category field, Category entries (alpha and beta) as series names, and the item names as category (X) axis labels. Here is instructions on how to insert the event code to your "pivot table data source" worksheet: 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.auto refresh pivot Excel 2007 pivot table: Count unique distinct records (rows) Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The […] becomes > How to add VBA code to your comment [vb 1="vbnet" language=","] Put your VBA code here.The following image shows you some of the data on worksheet "Data" 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. This procedure runs every time the worksheet calculates, whether or not the pivot table is involved. To make it run automatically when the pivot table updates, you could put this event procedure into the code module behind the sheet (to open the sheet’s code module, right click the sheet tab, click View Code). Name Then Update Chart From Pivot Table End If Application.
Read this post if you'd rather refresh your pivot table when you activate pivot table worksheet.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.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 That is why you have to manually change the source cell reference and refresh pivot table, this is very easy to forget.