Pivot table sort function

[werafa]

Hi all,
I an trying to create a function to sort a pivot table according to the data from a certain column. So far, so good.

Next, I want to automate this so it will step through and sort as many Row Label fields as may exist.

the following code almost does this, but is stepping through all the pivotfields contained in the pivotcache as far as I can tell.

[VBA]
Sub SortPivotData(myPivot As PivotTable)
'sort pivot by 2nd column (most recent year)
'in "Sum of Consumption" pivot range

Dim myfield As PivotField
'debug: for each is cycling through entire input data range columns
For Each myfield In myPivot.PivotFields
myfield.AutoSort xlDescending, "Sum of Consumption (kWh)", _
myPivot.PivotColumnAxis.PivotLines(2), 1
Next myfield
End Sub
[/VBA]

Can anyone tell me what to put in the for each loop to only sort visible row labels?

many thanks

[p45cal]

Check out .Orientation
myfield.orientation is 1 if it's a row field.
xlRowfield is an excel built in constant equal to 1.

so:
if myfield.orientation = xlRowField then
should process only those fields.

[Aflatoon]

Or simply loop through the Rowfields
[vba]For Each myfield In myPivot.RowFields[/vba]

[werafa]

hmm, this sounds good. If I suddenly go very quiet then you know that I am being very grateful. I believe it is a rowfield, so this should work

ps. I am related to Murphy......

Thanks

[werafa]

Many thanks to p45cal and Aflatoon - your suggestions worked, and your explanation has taught me something.

for everyone else who is looking for this answer:
Pivot tables allow you to sort a row label field according to a data field value. I had Sum of Consumption, year 1 and year 2, and wanted to sort by 'year 2' column only. The Pivot table has an Advanced Sort option that allows this, and this code cycles through as many Row Labels as may or may not exist and sets this sort order


[vba]Sub SortPivotData(myPivot As PivotTable)
'sort pivot by 2nd column (most recent year)
'in "Sum of Consumption" pivot range

Dim myField As PivotField

For Each myField In myPivot.RowFields
myfield.AutoSort xlDescending, "Sum of Consumption (kWh)", _
myPivot.PivotColumnAxis.PivotLines(2), 1
Next myField
Set myField = nothing
End Sub[/vba]