Pivot table data

[Guest]

I have a workbook where I have on sheet with data and several others with
pivot tables around that data. I change the entire data set by deleting all
the data from the cells on the data sheet and then pasting new data from
another workbook. When I refresh the pivot tables all the data in pivot
table updates perfectly, the problem is the drop down box on the left column
of the pivot table includes data that is no longer on the data worksheet.
This does not affect the data in the body of the pivot table but I would like
to clean it up so users don't have to scroll through all this garbage. Any
thoughts???

 

[Debra Dalgleish]

There are instructions here for clearing old items from a pivot table:

http://www.contextures.com/xlPivot04.html

 

[Guest]

Thanks for the link. The code worked once and then corrupted the file so I
could not even open the workbook. I tried it again on a different file and
had the same result. Do you have any other source for a solution?

 

[Debra Dalgleish]

I've used both procedures many times, and not had any problems with
corruption, and haven't seen any other reports of corruption.

Which code did you use, and what version of Excel are you using?
Is the file protected?