VBA code to select each pivot item in pagefield

[rumshar]

Hi All,
As you can see in the attached file, I have pivot table where I want to loop through each pivot item in pagefield and do something with the filtered data.
I tried with this code but it shows error(screenshot attached in file itself).
Kindly help me.

Sub LoopThroughPivotItems()
Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Set PT = ActiveSheet.PivotTables("PivotTable1")
Set PF = PT.PivotFields("Provider")

For Each PI In PF.PivotItems
PI.Visible = True ' Error is occuring here
Next PI

End Sub

May I know where I am going wrong?

With Regards
Rudra

 

[Abdul Matheen]

Hi Rumshar,

Just Change the Provider to Providers or something else it will work.

I have attached the workbook with changes.

Regards
Abdul Matheen

 

[rumshar]

Hi Rumshar,

Just Change the Provider to Providers or something else it will work.

I have attached the workbook with changes.

Regards
Abdul Matheen

Hi Abdul,
Thanks for you quick Response. I should have checked that PivotField was "Providers" not "Provider". Macro is running without error but it is not doing what it is suppose to. Although it is looping but it is not changing the filter in cell B1(Pivot Sheet), hence the data is also not changing. Can you pls cast your excel magic and make it work?
Thanks in advance
With Regards
Rudra

 

[Abdul Matheen]

HI Rudra,

As per the code you want to select all the items from the field "Providers", in the pivot table it is already in all items selected, hence it you will not see any difference in the pivot table, you just deselect some of the items and then run the macro it will select all. In the attached workbook i have deselected some of the items from the field providers. just check the Page field and run the macro.

Regards
Abdul Matheen

 

[rumshar]

HI Rudra,

As per the code you want to select all the items from the field "Providers", in the pivot table it is already in all items selected, hence it you will not see any difference in the pivot table, you just deselect some of the items and then run the macro it will select all. In the attached workbook i have deselected some of the items from the field providers. just check the Page field and run the macro.

Regards
Abdul Matheen

Oh, I See!
but my requirement is slightly different. Once it loads one pivot item, it has to clear that item before selecting another one.Sorry for troubling you again and again!!!!

 

[Abdul Matheen]

Hi Rudra,

Actually you cant deselect all the items from providers field, atleast one item has to be select, if you give me your requirement clearly i will try my level best.


Regards
Abdul Matheen

 

[rumshar]

Hi Abdul,
I need to click on drop down(Cell B1) and select only one Provider. Once the data gets filtered, I have to copy this data to another workbook(new workbook). I have to repeat these steps until I was finished with all the providers. I tried to record while doing so, but it gave me very much junk code and as you are aware, it is hard coded.Does it make sense?
Thank you very much
Rudra

 

[kchiba]

Rumshar,

Please ignore your pivot table and tell us what you want to achieve from the raw data, it may be possible to use some other method to achieve your outcome.

With your explanation, give some examples as well

kanti

 

[rumshar]

Rumshar,

Please ignore your pivot table and tell us what you want to achieve from the raw data, it may be possible to use some other method to achieve your outcome.

With your explanation, give some examples as well

kanti

Kanti,
I was able to fix this. Thank you.