Refresh Data In Pivot Table Not Working 4

[APElliott]

Hi

I've taken an old workbook that had a Data Field call "Sub Ref" the data in the old workbook contain values from 01 to 36.

My new workbook contains values 01 to 34 in this field.

The trouble is that when a code is run to 'shows all' in this field it doesn't work because it as remembered 35 and 36.

I can't seem to clear these! Even when I Refresh!

Am I doing something wrong?

Thanks

Andrew

 

[SkipVought]

Andrew,

What is the Data Source Range?

You ought to Insert/Name/Define the Data Source Table using the OFFSET function -- THEN the range will be DYNAMIC!

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

The issue is xl's retention of items that used to be in the pivot table but are not there now

Insert a module (if you havn't got any and run this:

for each sht in thisworkbook.worksheets
for each pt in sht.pivottables
pt.pivotcache.missingitemslimit = xlMissingItemsNone
next
next

or, if you know the name of the table

With Sheets("SheetName&quot.PivotTables("PTName&quot.PivotCache
.MissingItemsLimit = xlMissingItemsNone
.Refresh
End With

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[APElliott]

Amazing,

There gone!!!!

Cheers Geoff

I've already name the range Skip, but it's remembered the data from the old workbook! The problem is no longer though!

Cheers,

Andrew

 

[xlbo]

As I said - nothin' to do with data range - xl pivot tables can hold old data - the missingitemslimit can reset / clear these from the cache - depending on the option used. Can also be used to force xl to keep x number of old values as well (never come across a use for this tho ;-) )

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Geoff

Have another ==> STAR!

GOOD TIP!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DrBowes]

Star from me too.

 

[DrBowes]

Star from me too.

 

[xlhelp]

Geoff, since everyone is giving stars and billions of them in the galaxy, one from me too.

Actually, I learnt something new.

Deja Moo - "I have heard that bull before"