我在VBA中创建了一个充满字符串数据的数据透视表,但似乎无法折叠数据透视表中的所有字段,请问我该如何做?以下是我的源代码:
SrcData = ActiveSheet.Name & "!" & Range(Cells(1, 1), Cells(46, 3)).Address(ReferenceStyle:=xlR1C1)
StartPvt = Sheets("Key Controls").Cells(2, 5).Address(ReferenceStyle:=xlR1C1)
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
pvt.PivotFields("SOP Reference").Orientation = xlRowField
pvt.PivotFields("Key Control ID").Orientation = xlRowField
pvt.PivotFields("Key Control Name").Orientation = xlRowField
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Key Control ID") .Orientation = xlRowField .Position = 2 End With
假设您想要将整个字段折叠到“SOP Reference”,选择一个适当的单元格,比如A15
,代码将是Range("A15").Select ActiveSheet.PivotTables("PivotTable3").PivotFields("Key Control Name"). _ PivotItems("electron").DrillTo "SOP Reference"
您的表格是否看起来像这样(https://www.dropbox.com/s/51znhaab1z0lnno/pivot_q070716.xlsm?dl=0)? - skkakkar