Pivot Tables--HELP! 6

[dddivers]

Hi - I need help with 2 Pivot table problems, please:

1) Can't find any way to easily determine the source data feeding an existing Pivot Table!!??? Would think there would be a property setting that would tell me where the feeding data is coming from, but no!

2) Love the drop-down buttons on Pivot tables where you can show and hide specified data, but here's the problem: If I delete data from the source, then go refresh the Pivot table so it deletes out of it too, the data is still in the Drop-downs on the PT!!!! Data does delete off the actual PT report, but doesn't delete off the drop-downs!

 

[SkipVought]

Yup, nothing with built-in features can delete previous values.

To find Source Data, Activate PT Wizard, [Back] should display the source data range.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

run this code ONCE for each sheet with a pivot table that you want to get rid of old values in - note - this setting means that if any other values are deleted, they will disappear too:

Sub Clean_Pivots()
for each pt in activesheet.pivottables
  pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
next
End Sub

It is the missingitemslimit which sets whether "old" values are retained in the pivottable - once set, it stays set unless changed via code again

Caveat: This may not work in XL97 - should be fine for XL2000 onwards

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

 

[SkipVought]

geoff,

I was hoping that you would post this.

I could not remember this solution -- mebe 'cuz I run '97 on this machine ==>* ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[dddivers]

XLBO: Macro worked like a charm. Thanks so much--dddivers

 

[DeLaMartre]

Geoff,

Thanks so much for your typical altruistic brilliance.

Having recently taught a workshop on pivot tables at work, I am "all ears" (as Ross Perot once said) on additional pivot topics. Since I am the village idiot when it comes to VBA, however, I find postings such as you just made particularly valuable.

Thank you,



-Bob in California

 

[jfhewitt]

Try this site:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[DeLaMartre]

Good one, JF! I have not seen this site before.

Thanks!

-Bob in California

 

[lgbatdw]

I've added the "Clean Pivots" code on the Autpen event, but I must have a reference missing (but not sure which) because I get the message "Compile Error: Variable not defined." on the xlMissingItemsNone variable. I am running Excel 2000. I do have a reference for the Microsoft Office 9.0 Object Library & Office Web Components Function Library. I am not very Excel savy so any help would be greatly appreciated.

 

[xlbo]

You only need to run it once unless you are constantly creating new pivottables - Once the option is set, it requires code to change it again

To check whether the syntax has changed (I use xl XP/02)
goto the help file (VBA) and type in "missingitemslimit". This should bring up the missingitemslimit property. If it does, you can check the file for the correct syntax. If not, then I may be wrong about this property being available to xl2000

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

 

[lgbatdw]

Geoff
Thanks for the reply. I have looked for any reference to "missingitemslimit" without success, so I'm guessing (as you said) that the property is not available in xl 2000.
Thanks anyhow.
Linda in MN

 

[MJPPaba]

I've found that if you drag the pivot dimension out of the range then replace it it refrshes the data properly

cheers
matthew
crazypabs

K.I.S.S Keep It Simple Stupid