XLA routines: EE_PivotArrangeColFields

Nick's picture
Once you have created a pivot table, added the page fields, you can now add the col fields with EE_PivotArrangeColFields
Sub EE_PivotArrangeColFields(pt As PivotTable, FieldsArrayOrRange)
'- as above but for col fields
    Dim ptColField  As PivotField
    Dim intFld      As Integer
    Dim arr
'http://excelexperts.com/xla-routines-eePivotArrangeColFields    for updates on this sub routine

    For Each ptColField In pt.ColumnFields
        ptColField.Orientation = xlHidden
    Next ptColField
    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 = LBound(arr) To UBound(arr)
        On Error Resume Next
            With pt.PivotFields(arr(intFld))
                .Orientation = xlColumnField
                .Position = pt.ColumnFields.Count
            End With
        Err.Clear: On Error GoTo 0: On Error GoTo -1
    Next intFld
    Erase arr
    Set ptColField = Nothing
End Sub