Collapsing PivotField with Duplicate Occurence /Name

Question

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.

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://learn.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

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".

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).

Answer 1

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