Pivot Table Reset Page Fields to All 1

[herkiefan]

Hi,

I am new to pivot tables...I wish I had used them in the past though.

Anyway, I have built a pivot table that will allow the user to identify up to 20 different criteria to select the exact machines they want to view. The criteria drop down boxes are in the "Page Fields" section of the pivot table.

I was asked to put a button that would reset all of the criteria back to "All" without having to do it manually. Is there an easy way to do this?

Thank you in advance,

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

Hi,

Turn on your macro recorder and record setting ONE Page Field.

Then modify your code to loop thru all the Page Fields, using your recorded code in the loop as the basis for the statement.

Skip,

_[red][/red]

 

[xlbo]

Something to watch out for though - when setting the page field to "All" you actually need to set it to "" as the "All" doesn't really exist...

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

 

[SkipVought]

Geoff,

You do ACTUALLY set the Page Field to "(All)"

Setting to "" creates a new "" PivotTime.

Skip,

_[red][/red]

 

[herkiefan]

I'm sorry for being such a newbie to VBA, but I really do not know how to loop a macro. I've searched Google, Microsoft, and this site to give better clarity and have not had luck.

The code I have from changing just the first page field is:

Sub Reset_PIVOT()
 
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Manufacturer").CurrentPage _
        = "(All)"
    
End Sub

How do I get this to loop through the page fields?



“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

You need to ALSO become familiar with the Excel Object Model. If you understand objects' properties & methods, you can get around alot better in Excel VBA.

Sub Reset_PIVOT()
    Dim pvf As PivotField
    
    For Each pvf In ActiveSheet.PivotTables(1).PageFields
        pvf.CurrentPage = "(All)"
    Next
End Sub

Skip,

_[red][/red]

 

[herkiefan]

Thanks a lot Skip. I really appreciate all of the help I get from you. And you're right, I need to become familiar with the Excel Object Model. I did come up with the following code, (of course it does not work), but the code you supplied does. I'll get there eventually.

Sub Reset_PIVOT()
 Dim PVTField As PivotFields
 For Each PVTField In ActiveSheet.PivotTables("PivotTable2")
    CurrentPage = "(All)"
 Next PVTField
End Sub

Thanks again.

Your, hoping-to-obtain-just-one-star-someday-newbie,

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

1) not all PivotFields have the CurrentPage property. Only PageField PivotFields. (All PageFields are PivotFields but not all PivotFields are PageFields)

2) you did not reference the CurrentPage property to an object, like PVTField.CurrentPage = "(All)"

Skip,

_[red][/red]

 

[xlbo]

Skip - cheers for the response - that has changed since '97 then as I remember I had some issues with programming pivottables in that version of excel - not really done anything with them recently programming wise so didn't know it had been changed - cheers for the info

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