+ Reply to Thread
Results 1 to 5 of 5

Pivot Table not deleting previous data?

  1. #1
    Registered User
    Join Date
    09-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Pivot Table not deleting previous data?

    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

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot Table not deleting previous data?

    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.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot Table not deleting previous data?

    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.

    Please Login or Register  to view this content.
    whenever the PT is refreshed the lists should update to only show "current" values

  4. #4
    Registered User
    Join Date
    09-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Pivot Table not deleting previous data?

    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

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Pivot Table not deleting previous data?

    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 ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1