Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Refresh Data In Pivot Table Not Working 4

Status
Not open for further replies.
APElliott

APElliott

Technical User
Jul 9, 2002
165
0
0
GB
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 [pc]
 
Sort by date Sort by votes
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!
Code: 
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
:)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
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").PivotTables("PTName").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
 
Upvote 0 Downvote
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 [afro]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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"
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Pivot Table Issue
Replies
6
Views
127
Andrzejek
Andrzejek
GPnew
  • Question
Power Pivot Filter out Rows where data is not in all columns
Replies
14
Views
259
combo
combo
E
  • Question
Extract lines from table into new table
Replies
2
Views
189
SkipVought
S
ladyck3
  • Locked
  • Question
Excel 365 - Working with Tables - Calculating data from one worksheet to another. 2
2
Replies
30
Views
207
combo
combo
grnzbra
  • Locked
  • Question
Force Data Entry Format in Excel
Replies
10
Views
141
combo
combo

Part and Inventory Search

Sponsor

Back
Top