Put elements inside the Pivot Filter's field in the same column

Question

I try to create a Pivot table with 3 elements inside the Pivot filter field.

These usually are in the same column, when I create the Pivots manually, but with VBA they are in the same row and I cannot understand why.

The Pivot I want to create has the following fields:

Filters: Ship Date, Account name, Zip
Rows: SAP Code
Values: US Qty Shipped

Visually this is the result I obtain with my actual code:

Pivot with filters in the same row

And this is what I want to obtain (I made it manually without VBA, then I recorded the Macro following the same steps but I obtain the filters are always in the same column):

Pivot with filters in the same column

I tried also to select the "Compact Layout" from PivotTable tools without success.

    Sub Shipped_qty_US()

    Sheets("Tracker").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Tracker!R1C1:R72606C25", Version:=6).CreatePivotTable TableDestination:= _
        "Foglio7!R3C1", TableName:="Tabella pivot1", DefaultVersion:=6
    Sheets("Foglio7").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tabella pivot1")
        .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("Tabella pivot1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("Tabella pivot1").RepeatAllLabels xlRepeatLabels
    Sheets("Foglio7").Select
    Sheets("Foglio7").Name = "Shipped Qty US"
    Sheets("Shipped Qty US").Select
    Sheets("Shipped Qty US").Name = "PV Shipped Qty"
    With ActiveSheet.PivotTables("Tabella pivot1").PivotFields("Ship Date")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabella pivot1").PivotFields("Account Name")
        .Orientation = xlPageField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Tabella pivot1").PivotFields("Zip")
        .Orientation = xlPageField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Tabella pivot1").PivotFields("SAP Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabella pivot1").AddDataField ActiveSheet.PivotTables( _
        "Tabella pivot1").PivotFields("Qty Shipped"), "Somma di Qty Shipped", xlSum
    Range("B5").Select
    ActiveSheet.PivotTables("Tabella pivot1").DataPivotField.PivotItems( _
        "Somma di Qty Shipped").Caption = "US Qty Shipped"

End Sub

Thank you very much for the help!

Possible duplicate of [How do I position my pivot table filters above each other using VBA?](https://stackoverflow.com/questions/56533672/how-do-i-position-my-pivot-table-filters-above-each-other-using-vba) — Asger, Jul 31 '19 at 16:16

score 0 · Accepted Answer · answered Jul 31 '19 at 08:16

0

The arrangement of filters (which are PageFields) is defined by following parameters:

.PageFieldOrder = xlDownThenOver    ' 1=xlDownThenOver, 2=xlOverThenDown
.PageFieldWrapCount = 0             ' amount of page fields per column

So you just need to exchange these.

answered Jul 31 '19 at 08:16

Asger

3,822
3
12
37

Thank you for your answer! Can you give me a suggestion where place those strings? I understand what they stand for but I'm trying to figure out where placing them in the code, I'm very new to VBA. – Marco Veggo Scocco Jul 31 '19 at 08:53
OMG sorry for this stupid question, I just realized I was looking on the wrong part of the code! I have inserted "xlDownThenOver" in .PageFieldOrder, and "3" in .PageFieldWrapCount Thank you very much for the help, I checked the acceptance of your answer :) – Marco Veggo Scocco Jul 31 '19 at 09:24

Put elements inside the Pivot Filter's field in the same column

1 Answers1