Pivottable column selection 2

[gc6294]

I have a Pivottable (Excel 2003). In the drop-down selection box for the columns, I have a lot of old data values that are no longer used in my report. Is there a way to remove these old values from the drop-down selections?

 

[SkipVought]

Hi,

You'll have to run a macro like this on your workbook.

Copy and paste into a module. alt+F11 tabbles to the VB Editor.

Skip,

_[red][/red]

 

[GlennUK]

Hey Skip,

did you mean this:

Sub CleanMyPivots()
For each pc in thisworkbook.pivotcaches
   pc.missingitemslimit = xlmissingitemsnone
   pc.refresh
next
End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Yup!

Obviously xlMissingItemsInHeadConnectedToItemsInFingers

BYW, Keep Glen's macro in your PERSONAL.xls to pull it out whenever needed in the future.

Skip,

_[red][/red]

 

[xlbo]

'tis a very useful macro indeed.

Must've got me at least 4 or 5 stars so far !!!

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

 

[GlennUK]

Ha ha Geoff, that's right, it's your macro which I copied a long time ago. :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Have yet another!

Skip,

_[red][/red]

 

[gc6294]

Sorry for the stupid question, but can you explain how and where I install the macro?

Thank you all.....

 

[xlbo]

You need to go to your Visual Basic Editor (Alt+F11)
Go to the INSERT menu and insert a new module

copy and paste the code that Glenn has provided

Go back to excel and go Tools>Macros>Play Macro



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

 

[KenWright]

You could also check out the FAQs in the VBA forum:-

So where exactly do I put this macro code then??
faq707-5758

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[gc6294]

I tried to do the above, but get
Run-time error '1004'
Unable to read file

If I hit debug, pc.refresh is highlited...
Any ideas?

Thanks again....

 

[gc6294]

Please ignore previous message - figured it out myself..