Loop Through Pivot Tables And Remove Same Values

[Excelate]

Hi All,

I am trying to loop through all pivot tables in a sheet and remove all values fields in them which have the same name: "Total Net Spend" and "% Split".

I am trying the below code but it will only work on the first pivot and won't loop through all of them. How do I edit the code so that it will remove "Total Net Spend" and "% Split" columns on all the pivot tables in the sheet?


Sub Loop_Pivots()


Dim PT As PivotTable, PTField As PivotField
Set PT = Sheets("Sheet1").PivotTables("Pivot1")
With PT
.ManualUpdate = True
For Each PTField In .DataFields
PTField.Orientation = xlHidden
Next PTField
.ManualUpdate = False
End With
Set PT = Nothing


End Sub


Thanks,
Andrea

 

[James006]

Hello,

The macro you are showing ... does not loop ... !!! :wink:

Have you tried to insert For Each PT in Activesheet.PivotTables ...

HTH

 

[Excelate]

Thanks James. Sorry, I only have started using Macros so not sure how to go about this... I tried to add your bit to the code below but it doesn't work - any idea why?

Sub Loop_Pivots()


Dim PT As PivotTable, PTField As PivotField
Set PT = Sheets("Sheet1").PivotTables("Pivot1")
With PT
.ManualUpdate = True
For Each PT In ActiveSheet.PivotTables
For Each PTField In .DataFields
PTField.Orientation = xlHidden
Next PTField
Next PT
.ManualUpdate = False
End With
Set PT = Nothing


End Sub

 

[James006]

No problem ...:wink:

[COLOR=#333333]Sub Loop_Pivots()[/COLOR]
[COLOR=#333333]Dim PT As PivotTable
Dim PTField As PivotField[/COLOR]
[COLOR=#333333]For Each PT In ActiveSheet.PivotTables[/COLOR]
[COLOR=#333333].ManualUpdate = True[/COLOR]
[COLOR=#333333]     For Each PTField In .DataFields[/COLOR]
[COLOR=#333333]        PTField.Orientation = xlHidden[/COLOR]
[COLOR=#333333]     Next PTField[/COLOR]
[COLOR=#333333] .ManualUpdate = False[/COLOR][COLOR=#333333]
Next PT[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

HTH

 

[Excelate]

Still doesn't work it's giving me this error: invalid error or unqualified reference...

 

[James006]

Once you have all your pivot tables in place ... you can test following

Sub Loop_Pivots()
Dim PT As PivotTable
Dim PTField As PivotField
For Each PT In ActiveSheet.PivotTables
    For Each PTField In .DataFields
       PTField.Orientation = xlHidden
    Next PTField
Next PT
End Sub

HTH

 

[Excelate]

Still giving me the same error (invalid error or unqualified reference) this time on .DataFields. My original code does the job (gets rid of values fields) however, as you said, doesn't loop through all the pivot tables in the sheet. what would the code be for doing just that? For now it just does the job on the first pivot but then stops - need to understand how to make it loop until it finds pivot tables in the sheet...

 

[James006]

The Loop Instruction is :

For Each ... In ActiveSheet.PivotTables

 

[Excelate]

I see... Then I have no idea why the code isn't working. If I use the below it will give me an error message about .DataFields command being an invalid error or unqualified reference...