如何使用VBA将枢轴表过滤器放置在彼此之上？
如何使用VBA将枢轴表过滤器放置在彼此之上？
提问于 2019-06-10 21:19:06
基本上，我有多个标签放在一起，每个有多个枢轴，其中每个支点有多个过滤器内置。这些需要是可见的，以确认您正在看到的数据，因为非开发人员不知道如何访问或理解VBA代码。
我想要的是:数据透视表过滤器以列表形式相互覆盖：
我现在得到的是：
下面是code...please的一个简化版本，让我知道是否有更好的方法来做到这一点！
我尝试使用excel中的Record按钮来格式化我想看到的所有内容，但是一旦我实际运行了宏，过滤器就会并排而不是放在一起。
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回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-11 08:16:13
过滤器字段的顺序由参数PageFieldOrder定义，该参数可以是：
xlOverThenDown(= 2，以前的结果)xlDownThenOver(= 1，您想要的结果)
我还优化了您的代码：
- 通常，我不需要select or activate anything
- 我添加了两个变量来引用枢轴缓存和pivottable对象。
- 可以添加类似于其他枢轴字段的数据字段，但它们的名称必须在后面设置。
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56533672
复制相关文章
相似问题
腾讯云开发者
