Reset pivot table fields in multiple sheets

[manny_cb]

hi all..

i posted this originally in the general forum but i think vba is required for this so ill try here.. mods.. sorry if this counts as a repost.

basically i have pivot tables in diff pages
im trying to make a button to reset all the fields in all the pivot tables..

i tried recording a macro to do this but it only works for page fields..
it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....

this isthe code it gave:

Please Login or Register  to view this content.

i tried adding the following line to reset teh filter for the column field but it gives an error..

Please Login or Register  to view this content.

any ideas .. also i dont think this will be efficient for multiple sheets..
any other ideas ? help is greatly appreciated.. thanks

[rylo]

Hi

1) Can you please remember to wrap your code. Makes things much easier to read.

2) How about adding a loop

Please Login or Register  to view this content.

rylo

[manny_cb]

hey... sorry about that.. never posted code before .. now i know better

i tried the loop.. it fails on the

Please Login or Register  to view this content.

Runtime error '1004'
Unable to set the visible property of the PivotItem class


also im not sure what your code is doing...
im very new to vba so help is greatly appreciated..

the thing is that region is a column field and i want it to reset to "ShowAll"

lets forget about multiple sheets.. lets jus say theres one pivot table..
how do you reset the column fields to "showAll"

really appreciate the help

[manny_cb]

sorry my bad.. it is the row fields im trying to reset

[manny_cb]

Please Login or Register  to view this content.

i foudn this code somewhere
after u run the macro..it keeps looping
you have to press escape and end to stop it
but it works.. all the row fields get reset
i guess it keeps looking for more pivot tables in the workbook

any ideas ??

[Cheeky Charlie]

Please Login or Register  to view this content.

How about that?

You can choose (lesser of two evils...) to display alerts or not - one way the thing will get all flustered if there is data in your sheet, the other way it will just overwrite it...

HTH

(Actually, this one's a keeper, think it will be quite handy - good idea!)

[Cheeky Charlie]

Also, general notes; really good work on trying ="(Show all)" and the other bits you've posted - you're going to learn super fast using logical extrapolations like that and giving it a go.

A tip for this kind of thing (cheesy mantra coming up) always use option explicit. Then define your variables as your write your code, that way, when you put a . at the end of one of these things, you get a whole host of "tips" on what to try next.

That's how I worked out I didn't need to cycle through every item in every pivot field!

HTH

[manny_cb]

thanks for the help but problem persists...


Shouldnt the

Please Login or Register  to view this content.

be before the

Please Login or Register  to view this content.

it didnt work though.. same thing.. the cursor turns into a hour glass and eventually i have to use esc to break the loop

if i choose debug .. the line

Please Login or Register  to view this content.

gets higghlted.. error says unable to setproperty showallitems

:S

[manny_cb]

thanks for the pointers

[rylo]

Hi

have you not got a working solution? Not sure from your last post.

If not, then attach an example workbook with your pivot table and code so we can review.

rylo

[Cheeky Charlie]

Worked for me although the pivot table I used to test it on was very simple (created for the job).

I now use it as a handy "reset" tool for other pivot tables.

RE: defining variables; again, worked for me - someone clever might step in and explain when's best to define variables in a macro and when to put them at the top of a module.

[manny_cb]

hey guys... thanks for all the help.. but still no working solution.. maybe ur solution didnt work cuz im in excel 2002 ??

[Cheeky Charlie]

Try this:

Please Login or Register  to view this content.

I didn't test my first code thoroughly enough...

[Cheeky Charlie]

Further to this (to clarify)
showallitems doesn't show all items, it checks "show items with no data" - not what was asked for.

looping through hiddenitems stops the macro crashing on the page fields at the top.