Pivot Table Column Field showing items not in my source data? 2

[arby123]

Hi, hope someone can help. I've an Excel pivot table/chart, the source data is amended daily, and historically the values in one column were different. They've now changed, I've refreshed the pivot - but in the drop down field for this column it still lists all the old values (as well as the new ones). I don't want to have to keep re-creating a pivot table each time the source data changes.

Any ideas?

Richard.

 

[xlbo]

This is do do with the pivotCACHE retaining older values

Run the following code ONCE only with the sheet that contains the pivottable active

Sub CleanPivotData()
activesheet.pivottables(1).pivotcache.missingitemslimit = xlmissingitemsnone
end sub

to get more info, type
missingitemslimit
into the VBA help search engine

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[ETID]

A quick but dirty way...

rename the offending field in the source data

I.E.

field name: date received to date_received

refresh the pivot

change the name back to the original

refresh the pivot

 

[ETID]

Hi XLBO...

Does this work with all versions of excel?

 

[xlbo]

Only those that have a pivotcache I believe - should work fine on 2000/XP - not sure about 97 'cos by the time I learned about it, we had moved to XP ;-)

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[dkaffy]

ETID -

Have searched all over web and tek-tips and found your suggestion to work perfectly! Your suggestion saved me having to recreate six different pivot tables and charts!

Thanks!!

 

[xlbo]

kdaffy - if you have 2000 or higher, you should really use the missingitemslimit as it would mean not having to change the field name every time...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

XLBO,

I am curious,...about this line in your response.

"Run the following code ONCE only with the sheet that contains the pivottable active"

Will rotten things happen if this is run more than once or is it just redundant?

thanks

 

[xlbo]

It's just redundant - once the limit has been set, it stays that way - there is no harm in running it again but it's kinda pointless ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question