P45cal was right about .Orientation not working as expected
This might be a little more better
Since you can rename field names in the PT and data values look like "Sum of …." or "Average of …", .SourceName might be better
Option Explicit
Dim o As Long
Sub ListPT()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
o = 2
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PageFields
Call AddToSummary(pf, "Page")
Next
For Each pf In pt.RowFields
Call AddToSummary(pf, "Row")
Next
For Each pf In pt.ColumnFields
Call AddToSummary(pf, "Column")
Next
For Each pf In pt.DataFields
Call AddToSummary(pf, "Data")
Next
Next
Next
End Sub
Private Sub AddToSummary(F As PivotField, s As String)
Dim v As Variant
Dim c As String
Dim i As Long
With Worksheets("Summary")
.Cells(o, 1).Value = F.Parent.Parent.Parent.Name
.Cells(o, 2).Value = F.Parent.Parent.Name
.Cells(o, 3).Value = F.Parent.Name
.Cells(o, 4).Value = F.SourceName
.Cells(o, 5).Value = s
End With
o = o + 1
End Sub