Displaying filters horizontally

Question

I have created a macro to create a pivot table. I have recorded the macro and when I recorded it, all the filters on pivot macro stacked (image 1). However when I run the macro it is putting all the filters horizontally instead of stacking them on top of each other (image2). I would like my macro to run and show like the image 2 (stacked vertically).

'
' oepivot Macro
'

'
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R218205C31", Version:=8).CreatePivotTable TableDestination:= _
        "CrossCheck!R1C1", TableName:="PivotTable9", DefaultVersion:=8
    Sheets("CrossCheck").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable9")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable9").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Accounting Period")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Object")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Object Class")
        .Orientation = xlPageField
        .Position = 2
    End With
    Application.Width = 886.5
    Application.Height = 661.5
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Fiscal Year")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("PE FILTER")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Jrnl Posting Code"), "Count of Jrnl Posting Code", _
        xlCount
End Sub

Maybe increment the value for `Position` each time you add a Page Field? — Tim Williams, Apr 04 '23 at 21:02
Also look at the pivot table `PageFieldOrder` (you have 2 which is `xlOverThenDown` but you probably want `xlDownThenOver` or 1) and `PageFieldWrapCount` properties. — Tim Williams, Apr 04 '23 at 21:17

score 2 · Accepted Answer · answered Apr 04 '23 at 21:30

See comments above, and below - note you can get a reference to the pivot table directly returned from the CreatePivotTable method. That an a helper method can make your code much easier to read and maintain.

Sub test()
    
    Dim pc As PivotCache, pt As PivotTable
    
    Application.CutCopyMode = False
    
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                       SourceData:="Sheet1!R1C1:R218205C31", Version:=8)
    With pc
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    
    Set pt = pc.CreatePivotTable(TableDestination:="CrossCheck!R1C1", _
                            TableName:="PivotTable9", DefaultVersion:=8)
    With pt
        'snipped for brevity
        .PageFieldOrder = xlDownThenOver '<<<<<<
        .PageFieldWrapCount = 0
        'snipped for brevity
    End With
    
    pt.RepeatAllLabels xlRepeatLabels
    
    SetField pt, "Accounting Period", xlPageField, 1
    SetField pt, "Object", xlPageField, 2
    SetField pt, "Object Class", xlPageField, 3
    SetField pt, "Fiscal Year", xlPageField, 4
    
    SetField pt, "PE FILTER", xlRowField, 1
    
    pt.AddDataField pt.PivotFields("Jrnl Posting Code"), _
                   "Count of Jrnl Posting Code", xlCount
End Sub

'shortcut for adding a field to a pivottable
Sub SetField(pt As PivotTable, fldName As String, _
          fldType As XlPivotFieldOrientation, fldPos As Long)
    With pt.PivotFields(fldName)
        .Orientation = fldType
        .Position = fldPos
    End With
End Sub

Displaying filters horizontally

1 Answers1