重复出现的PivotField崩溃/名称 [英] Collapsing PivotField with Duplicate Occurence /Name

问题描述

我有一个数据透视表，我想在其中两次使用 PivotField("Amount").一次作为一行，另一个作为值/数据字段.原因是对于给定的月份/类别，我具有总计"，但是随后，我希望能够对其进行扩展以查看各个类别的交易.

I have a Pivot Table where I want to use the PivotField("Amount") two times. Once as a row and another as a value/datafield. The reason being is I have "Total Amounts" for given months/categories, but then I want to be able to expand this to see individual categories transactions.

我的问题是我无法折叠PivotField，现在，当我扩展类别时，它会自动扩展交易，这不是期望的反应.我相信这与PivotField的重复出现有关名称与我使用的相同代码可以折叠其他字段.

My issue is I am unable to collapse the PivotField and now when I expand a category it automatically expands the transactions which is not the desired reaction. I believe this is related to duplicate occurrence of the PivotField name as the same code I have works to collapse other fields.

这是完整的数据透视表代码和数据透视表示例的屏幕截图.

Here is the complete PivotTable code and a screenshot of the PivotTable example.

Sub CreatePivotTable()

Categories

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

On Error Resume Next
Application.DisplayAlerts = False
'Worksheets("PivotTable").Delete
SheetExists = WorksheetExists("PivotTable")
If Not SheetExists = True Then
    Sheets.Add Before:=PSheet
    PSheet.Name = "PivotTable"
End If
Application.DisplayAlerts = True
Set PSheet = ThisWorkbook.Worksheets("PivotTable")
Set DSheet = ThisWorkbook.Worksheets("Transactions")  '''Source Data Sheet Name'''

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

PTableExists = ExistPivot("PivotTable")
If PTableExists = True Then
'MsgBox "Deleting PTable!"
 Call DeletePivotTable("PivotTable", "PivotTable")
End If

Set PCache = ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PivotTable")

Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")

'https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.orientation
With PSheet.PivotTables("PivotTable").PivotFields("Date")
 .Orientation = xlRowField
 .Position = 1
 .DataRange.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
End With

With PSheet.PivotTables("PivotTable").PivotFields("Categories")
 .Orientation = xlRowField
 .Position = 3
End With

With PSheet.PivotTables("PivotTable").PivotFields("Desc")
 .Orientation = xlRowField
 .Position = 4
End With

With PSheet.PivotTables("PivotTable").PivotFields("Amount")
 .Orientation = xlRowField
 .Function = xlSum
 .Position = 5
End With

With PSheet.PivotTables("PivotTable").PivotFields("Amount")
 .Orientation = xlDataField
 .Function = xlSum
 .Position = 1
End With

PSheet.PivotTables("PivotTable").PivotFields("Date").ShowDetail = False
PSheet.PivotTables("PivotTable").PivotFields("Categories").ShowDetail = False

''''This line doesn't work''''
PSheet.PivotTables("PivotTable").PivotFields("Amount").ShowDetail = False

Worksheets("PivotTable").Activate

End Function

要制作完整的MCVE，这里有一些示例数据，请将其放在名为"Transactions"的工作表中，然后运行上面的代码以创建数据透视表进行测试.

To make a complete MCVE here is some sample data, place this in a worksheet named "Transactions" and run the above code to create the PivotTable for testing.

Date        Desc        Amount   Categories
10/15/2019  CAN TIRE MC     50  Credit Payment
10/25/2019  PC MASTRCRD     50  Credit Payment

下图显示了当我在信用付款"类别上展开时会发生什么.

The image below shows what happens when I expand on the category "Credit Payment".

有人对此有解决方案吗?我读过使用 DrillTo 而不是 ShowDetail ，但是我完全无法使用它，但是现在我想知道这是否可以解决问题，或者我可以在某种程度上，我想折叠的PivotField(以及执行该操作的语句)更加具体.

Does anybody have a resolution to this? I read to use DrillTo and not ShowDetail but I wasn't able to get that to work at all, but now I wonder if that would be the fix or if I can somehow be more specific with the PivotField I want to collapse (and the statement to do that).

推荐答案

它有效，请尝试他的代码.

It works, try his code.

' doesn't works
PSheet.PivotTables("PivotTable").PivotFields("Desc").PivotItems.ShowDetail = False

'works
PSheet.PivotTables("PivotTable").PivotFields("Desc").ShowDetail = False

' works
Dim pivotItm As PivotItem
 For Each pivotItm In PSheet.PivotTables("PivotTable").PivotFields("Desc").PivotItems
  pivotItm.ShowDetail = False
 Next pivotItm

这篇关于重复出现的PivotField崩溃/名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！