XLA routines: EE_PivotArrangePageFields

Nick's picture
Once you have created a pivot table, the next thing you want to do is add to the page fields EE_PivotArrangePageFields allows you to do this passing in a range containing the fields you want added
Sub EE_PivotArrangePageFields(pt As PivotTable, FieldsArrayOrRange)
'- takes an array of fields and moves them to the page area
'- takes the pivot to operate on
'- resume next through errors
'- remove page fields that are not on the list
'- ensures the order of the array is reflected in the pivot

'http://excelexperts.com/xla-routines-eePivotArrangePageFields    for updates on this sub routine

    Dim ptPageField As PivotField
    Dim intFld      As Integer
    Dim arr
    For Each ptPageField In pt.PageFields
        ptPageField.Orientation = xlHidden
    Next ptPageField
    Select Case TypeName(FieldsArrayOrRange)
        Case "Variant()", "String"
            arr = FieldsArrayOrRange
        Case "Range"
            If FieldsArrayOrRange.Cells.Count = 1 Then
                ReDim arr(0)
                arr(0) = FieldsArrayOrRange
                arr = Application.Transpose(FieldsArrayOrRange)
            End If
        Case Else
    End Select
    For intFld = UBound(arr) To LBound(arr) Step -1
        On Error Resume Next
            With pt.PivotFields(arr(intFld))
                .Orientation = xlPageField
                .Position = pt.PageFields.Count
            End With
        Err.Clear: On Error GoTo 0: On Error GoTo -1
    Next intFld
    Erase arr
    Set ptPageField = Nothing
End Sub