I'm trying to automate the following code since I have to do the same thing for PivotTable0 to PivotTableX as well as PivotTable0A to PivotTableXA. Especially since the number of Pivot Tables often changes and I don't want to update (copy paste) the same code for all tables in the sheet.
For Each PivotFields In ActiveSheet.PivotTables("PivotTable0").DataFields
PivotFields.Orientation = xlHidden
Next PivotFields
ActiveSheet.PivotTables("PivotTable0").AddDataField ActiveSheet.PivotTables( _
"PivotTable0").PivotFields(Country), " " & Country, xlAverage
For Each PivotFields In ActiveSheet.PivotTables("PivotTable0A").DataFields
PivotFields.Orientation = xlHidden
Next PivotFields
ActiveSheet.PivotTables("PivotTable0A").AddDataField ActiveSheet.PivotTables( _
"PivotTable0A").PivotFields(Country), " " & Country, xlAverage
For Each PivotFields In ActiveSheet.PivotTables("PivotTable1").DataFields
PivotFields.Orientation = xlHidden
Next PivotFields
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Country), " " & Country, xlAverage
For Each PivotFields In ActiveSheet.PivotTables("PivotTable1A").DataFields
PivotFields.Orientation = xlHidden
Next PivotFields
ActiveSheet.PivotTables("PivotTable1A").AddDataField ActiveSheet.PivotTables( _
"PivotTable1A").PivotFields(Country), " " & Country, xlAverage
Would be nice to have something like
For Each pt in ActiveSheet.PivotTables
For Each pf in pt.PivotFields
pt.DataFields
pf.Orientation = xlHidden
Next
pt.PivotFields(Country), " " & Country, xlAverage
Next
but that gives me an error and as soon as I leave the following line, it adds a space before the brackets:
pt.PivotFields (Country), " " & Country, xlAverage
Thanks, Juschk
Bookmarks