Overview values pivot table

[Philips]

Hi all,

I'm trying to make an overview of all pivot values used in my current worksheet.
I tried to search on the "xlDataField" and getting all information, like wbName, wsName, ValueName, ... but I keep getting a blank overview...

any suggestions??


Thanks in advance!

[p45cal]

I'm trying to make an overview of all pivot values used in my current worksheet

I'm not sure what pivot values are but this will run through all pivot tables on the active sheet:

  For Each pt In ActiveSheet.PivotTables
    Debug.Print pt.Name, pt.Parent.Name, pt.Parent.Parent.Name, pt.TableRange2.Address, pt.DataBodyRange.Address
  Next

and provide a bit of information on each in the Immediate pane of the VBEditor

[Philips]

Hi, thanks for the fast reaction!
I'm trying to get an overview off all fields used in the pivots in my workbook.
In my code, I use a case to get all fields from xlPageField, xlRowField, xlColumnField; which results in:
Knipsel.jpg

If I integrate the xlDataField parameter, my code crashes

[p45cal]

Show the code here, it'll probably just need a tweak.

[Paul_Hossler]

I think .DataFields fails if there aren't any.

You could use .DataFields.Count > 0 then ….

But try this as a start

Option Explicit
Sub ListPT()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim o As Long
    
    
    o = 2
    
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PivotFields
                Worksheets("Summary").Cells(o, 1).Value = ws.Name
                Worksheets("Summary").Cells(o, 2).Value = pt.Name
                Worksheets("Summary").Cells(o, 3).Value = pf.Name
                Select Case pf.Orientation
                    Case xlColumnField
                        Worksheets("Summary").Cells(o, 4).Value = "Column"
                    Case xlDataField
                        Worksheets("Summary").Cells(o, 4).Value = "Data"
                    Case xlHidden
                        Worksheets("Summary").Cells(o, 4).Value = "Hidden"
                    Case xlPageField
                        Worksheets("Summary").Cells(o, 4).Value = "Page"
                    Case xlRowField
                        Worksheets("Summary").Cells(o, 4).Value = "Row"
                End Select
                
                o = o + 1
            Next
        Next
    Next
    
    

End Sub

[Philips]

Hi,

this is the code I use at this moment:

For Each ws In wb.Worksheets
If ws.PivotTables.Count > 0 Then
For Each pt In ws.PivotTables


For Each pf In pt.PivotFields
Select Case pf.Orientation
Case xlPageField: strLoc = "Filter"
Case xlRowField: strLoc = "Row"
Case xlColumnField: strLoc = "Column"
Case xlDataField: strLoc = "Value"
Case Else: strLoc = "" 'only list row, column, filter
End Select

If strLoc <> "" Then
strPF = pf.Name
strPI = PFItems & " Items"
Select Case bAll
Case True: strVis = "Y"
Case Else: strVis = "N/A"
End Select

With wsList
.Range(.Cells(lRow, 1), _
.Cells(lRow, lCols)).Value = Array(wb.Name, ws.Name, strLoc, strPF)
End With
lRow = lRow + 1
End If
Next pf
Next pt
End If


Debug.Print ws.Name
Next ws

[p45cal]

I'm out of time today so in haste (I will come back to it tomorrow):
In my experience (not comprehensive) I've never seen a pivot field's .Orientation property return xlDataField (enumerates to 4); even if you write code to make it so, and as a result you see the field in the Values/Data area of the pivot table, when you look at the property (and when you query it) it always returns xlHidden.
I think you may be better served if you use a For Each loop for each of .RowFields, .PageFields, .ColumnFields and .DataFields:
(Air code because I'm in a hurry)

For each pf in pivottable.rowfields
'get your names here (they're all in the rows section)
next pf
For each pf in pivottable.columnfields
'get your names here (they're all in the column section)
next pf
For each pf in pivottable.pagefields
'get your names here (they're all in the filter/page section)
next pf
For each pf in pivottable.datafields
'get your names here (they're all in the values/data area)
next pf

If there are no pivotfields in any particular section the code shouldn't falter.
You'll get your pivot fields grouped and in any order you want (rows,columns etc.)

[Paul_Hossler]

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

[Philips]

thanks! It is working now