如何使用VBA将数据透视表过滤器彼此放置在上方? [英] How do I position my pivot table filters above each other using VBA?

问题描述

基本上，我有多个选项卡，每个选项卡都具有多个枢轴，每个枢轴都内置有多个过滤器.由于非开发人员不知道这些内容，因此需要将其可见以确认您看到的数据，如何访问或理解VBA代码.

Basically, I have multiple tabs I am putting together, each with multiple pivots, where each pivot has multiple filters built in. These need to be visible to confirm the data you are seeing, since non-developers wouldn't know, how to access or understand VBA code.

我想要的:数据透视表过滤器以列表形式彼此重叠:

What I want: Pivot table filters to be over each other in a list form:

我目前得到的是:

这是代码的简化版本...请让我知道是否有更好的方法！

Here is a simplified version of the code...please let me know if there is a better way of doing this!

我尝试在excel中使用记录宏"按钮并对所有内容进行格式化，但是，一旦我实际运行宏，过滤器就会并排放置，而不是彼此叠放.

I tried using the Record Macro button in excel and format everything as I wanted to see, but once I actually run the macro, the filters are side by side rather than on top of each other.

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

推荐答案

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

The order of the filter fields is defined by the parameter PageFieldOrder, which can be:

• xlOverThenDown(= 2，您以前的结果)
• xlDownThenOver(= 1，您想要的结果)

• xlOverThenDown (= 2, your previous result)
• xlDownThenOver (= 1, your desired result)

我另外优化了您的代码:

I optimized your code additionally:

• 通常，没有必要选择或激活任何内容
• 我添加了两个变量来引用数据透视表和数据透视表对象
• 可以像添加其他数据透视字段一样添加数据字段，但之后必须设置其名称

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

这篇关于如何使用VBA将数据透视表过滤器彼此放置在上方?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！