PIVOT TABLE PROBLEM 1

[MarkBeck]

I operate a pivot table whose source data keeps changing. The problem i have is that while the NAME field keeps changing with new names, the list under the NAME HEADING in the pivot table keeps growing! it does not delete names that are no longer in the source data list.

I often pick names from the heading to restrict the results. I now have to pick through 200 names or so when the source data only has 27 names.

Can someone help?

 

[ThomasLafferty]

Probably all you need to do is requery your data source. Use the red exclamation mark on the Pivot Table toolbar. [!]![/!]

Tom

Born once die twice; born twice die once.

 

[MarkBeck]

Tried all sorts of Refereshers to no avail.

 

[Gavona]

Seems to depend what version of Excel you are using. Have a look at this. thread707-438403.
Do let us know if it works for you as I never did get it to work back then.


Gavin

 

[SkipVought]

Activate the sheet containing the PT. I assume that there is only ONE PT on this sheet...

with Activesheet.pivottables(1).pivotcache
   .MissingItemsLimit = xlMissingItemsNone
   .refresh
end with

Skip,

_[red][/red]

 

[MarkBeck]

Skip

Did not change anything.

One think i should say; When i drag theheading to the top on line 2, it only contains current data. However, as soon as i drag it back to the PT header row, it contains ALL.

Is there anything under Table Option i should manipulate?

 

[xlbo]

MArk - that is the solution for the problem you have described. Please describe how you implemented Skip's solution as we may be able to see if there was an error somewhere along the line

Other than that, in your table options, do you have "Show values for items with no data" ticked ? if so, untick it.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MarkBeck]

I opened a module and pasted the code. I refreshed afterwards, Saved closed & opened. Nothing.

There is no MacroName() to start the code.

>"Show values for items with no data". I dont see this in my PT Options.

Thanks Geoff

Mark

 

[SkipVought]

"There is no MacroName() to start the code."

sub MyNameForThisMACRO()
with Activesheet.pivottables(1).pivotcache
.MissingItemsLimit = xlMissingItemsNone
.refresh
end with
end sub
give it whatever name yo like

Skip,

_[red][/red]

 

[MarkBeck]

Tnanks Skip! You're a star!