excel - 如何使用 VBA 将我的数据透视表过滤器置于彼此之上？

Question

基本上，我有多个选项卡放在一起，每个选项卡都有多个枢轴，每个枢轴都有多个内置过滤器。这些需要可见以确认您看到的数据，因为非开发人员不知道如何访问或了解 VBA 代码。

我想要什么:数据透视表过滤器以列表形式相互叠加:



我目前得到的:



这是代码的简化版本...如果有更好的方法，请告诉我!

我尝试在 excel 中使用“记录宏”按钮并按照我想看到的格式设置所有内容，但是一旦我实际运行宏，过滤器就会并排而不是彼此重叠。

Sub Macro5()
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False


    Dim dataname As String
    Dim datasheetname As String
    Dim pivotsheetname As String

    dataname = ActiveSheet.ListObjects(1).Name
    datasheetname = ActiveSheet.Name
    pivotsheetname = datasheetname & " Pivot"


    Sheets.Add

    ActiveSheet.Name = pivotsheetname

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        dataname, Version:=6).CreatePivotTable TableDestination:= _
        "'" & pivotsheetname & "'!R3C1", TableName:="PivotTable15", 
    DefaultVersion:=6
    Sheets(pivotsheetname).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable15")
        .ColumnGrand = False
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = False
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 3
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("PivotTable15").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable15").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable15").PivotFields("Billable?")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable15").PivotFields("Billed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable15").PivotFields("Amount")
    enter code here       .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable15").AddDataField 
    ActiveSheet.PivotTables( _
        "PivotTable15").PivotFields("Qty"), "Sum of Qty", xlSum
End Sub

Answer 1

过滤器字段的顺序由参数 PageFieldOrder 定义。 ，可以是:

xlOverThenDown (= 2, 你之前的结果)

xlDownThenOver (= 1，您想要的结果)

我还优化了您的代码:

通常，我不需要select or activate anything

我添加了两个变量来引用数据透视缓存和数据透视对象

可以像其他数据透视字段一样添加数据字段，但必须在之后设置它们的名称

Sub GenerateNewPivottable()
    Dim datasheetname As String
    Dim dataname As String
    Dim pivotsheetname As String
    Dim pc As PivotCache
    Dim pt As PivotTable

    Application.CutCopyMode = False

    dataname = ActiveSheet.ListObjects(1).Name
    datasheetname = ActiveSheet.Name
    pivotsheetname = datasheetname & " Pivot"

    Sheets.Add
    ActiveSheet.Name = pivotsheetname

    Set pc = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataname)
    With pc
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault ' better: xlMissingItemsNone
    End With

    Set pt = pc.CreatePivotTable( _
        TableDestination:="'" & pivotsheetname & "'!R3C1", _
        TableName:="PivotTable15")

    With pt
        .ColumnGrand = False
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = XlOrder.xlDownThenOver
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = False
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 3
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
    End With


    With pt.PivotFields("Billable?")
        .Orientation = xlPageField
        .Position = 1
    End With
    With pt.PivotFields("Billed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With pt.PivotFields("Amount")
        .Orientation = xlPageField
        .Position = 1
    End With

    With pt.PivotFields("Qty")
        .Orientation = xlDataField
        .Function = xlSum
        .Name = "Sum of Qty"
    End With

End Sub