Pivot Table not deleting previous data?

**Vickii** · 09-14-2009, 01:28 AM

Hi guys, I am fairly new to Excel (only experience was very basic schoolwork), having recently employed as a Grad in my company, I have been given some Excel tasks.

My question is, I am now working on a Pivot Table. Every month, we import a new set of data which refreshes the Pivot Table. However, some of the data values, might be changed during the monthly imports.

The problem I am having is, some of the names in the new import may be very similar to the previous month's, which makes some of our results go very wrong. The previous person has since left the company, and apparently everyone knew that the data wasnt refreshed properly, but nothing has been done. I have been asked to fix this, but have no idea how I can do it

Any help would be much appreciated! Thanks, Vicky

**DonkeyOte** · 09-14-2009, 02:15 AM

If you right click on the PT and choose Options - go to last Tab "Data" - set "Number of items to retain per field" to None.

**DonkeyOte** · 09-14-2009, 02:26 AM

Just realised you're using 2003 rather than 2007... the same can be achieved but you probably need VBA, eg right click on Tab containing the PT, choose View Code and paste the below into the resulting window ensuring that Macros are enabled thereafter.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Target.PivotCache.MissingItemsList = xlMissingItemsNone
End Sub

whenever the PT is refreshed the lists should update to only show "current" values

**Vickii** · 09-14-2009, 03:39 AM

Hi DonkeyOte,

Thanks for your help, just checked the macro, and the line below already exist in the spreadsheet, however it still wasnt doing its thing

Target.PivotCache.MissingItemsList = xlMissingItemsNone

I tried creating a separate sub for it, the old data still exist

**DonkeyOte** · 09-14-2009, 03:45 AM

We'd need to know exactly where the line already exists etc... for ex. the code I gave should reside in the PT update event for the given sheet object (ie the sheet on which the PT resides)

Are you able to upload the file (you may need to zip first) ?

Are your Macros Enabled ?