I have a pivot table with multiple data fields. However, I do not
want to display all the data fields at the same time. I would like to
write a macro which loops through all the data fields and refreshes
the table upon doin so. At any given time I only want one data field
to be displayed.

My data fields are named Aco, Bco, Cco, and Dco.

So if Aco is shown, I want Bco, Cco and Dco to be hidden. Is ther
any easy way to put these in a loop so that I can do this?

TS

wrote:
So if Aco is shown, I want Bco, Cco and Dco to be hidden. Is ther
any easy way to put these in a loop so that I can do this?


You will need some form of user interaction to say "ready to look at
next item" - maybe a button on the worksheet.

I guess the following (untested) procedure might do it:

Sub NextData()
Dim vFields
Dim iField As Integer
vFields = Array("Aco", "Bco", "Cco", "Dco")
With ActiveSheet.PivotTables("PivotTable5")
For iField = LBound(vFields) To UBound(vFields)
If .PivotFields(vFields(iField)).Orientation = xlDataField Then
.DataFields(1).Orientation = xlHidden
If iField < UBound(vFields) Then
.PivotFields(iField+1).Orientation = xlDataField
Else
.PivotFields(LBound(vFields)).Orientation = xlDataField
End If
Exit For
End If
Next
End With
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup