我有一个数据透视表,有几个字段,但是我只想折叠一个数据透视字段。我尝试了几次都不成功,所以需要一些帮助。
Dim pt As PivotTable
Dim pr As Range
Dim pc As PivotCache
Set pr = dataSht.UsedRange
Set pc = wb.PivotCaches.Create(xlDatabase, SourceData:=pr)
Set pt = pc.createPivotTable(wb.Worksheets(shtName).Range("A1"), strPivotName)
With pt
.PivotFields("One").Orientation = xlRowField
.PivotFields("One").Subtotals(1) = False
.PivotFields("Two").Orientation = xlRowField
.PivotFields("Two").Subtotals(1) = False
.PivotFields("Three").Orientation = xlRowField
.PivotFields("Three").Subtotals(1) = False
.PivotFields("Four").Orientation = xlRowField
.PivotFields("Four").Subtotals(1) = False
With .PivotFields("Sum of $$$ (000)")
.Orientation = xlDataField
.Caption = "% Total"
.Calculation = xlPercentOfParent
.BaseField = "Two"
.NumberFormat = "0.00%"
End With
我希望折叠最后一个字段,即Four。在我的第一个With的结尾添加
.PivotFields("Four").ShowDetail = False
会导致应用程序定义或对象定义错误。 "ShowDetails"也会出现同样的错误。另外,我尝试使用DrillTo,但我不太理解如何使用,在End With之后按以下方式尝试:
Dim pi as PivotItem
pi.DrillTo Field:= "Three"
我又遇到了一个错误。对象变量或者块变量未设置。
我还在努力理解VBA,如果有人可以帮帮我就太好了!
With
块内工作,但无济于事。谢谢 :) - FreeSoftwareServers