• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA all fields used in all the pivot tables in the workbook.

Tetonne

Member
Hello,
I have an excel file consisting of :
- I have an excel file consisting of: a data tab and x analysis tabs with pivot tables based on the same range of data present in the 1st data tab.

I would like to have a vba macro that would allow me to identify the fields used in all the pivot tables in the workbook.

The data used in :
- filter
- columns
- rows

The idea is to easily identify the fields (in the data tab) that are used the most and those that are not used or not very much.
Thanking you in advance
 
So you are not interested in value fields?

What output do you want? Just a list of field names and counts of usage?
 
Thanks Debaser for your interest.
not interested in value fields but only fields
knowing fields names for any pivot in tab named would be great.
thanks for your help :)
 
OK then. First, you will need to download the Dictionary class from here: https://github.com/VBA-tools/VBA-Dictionary/releases

Unzip and drag the Dictionary.cls file into your workbook project in the VB Editor (or use the File - Import File... option in the VB Editor)

Then add this code to a new module:

Code:
Sub SummarisePivotFields()
   Dim SummaryDict As Dictionary
   Set SummaryDict = New Dictionary
  
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
      Dim pt As PivotTable
      For Each pt In ws.PivotTables
         Dim pf As PivotField
         For Each pf In pt.PivotFields
            If Not IsValuesField(pf) Then
               Select Case pf.Orientation
                  Case xlRowField, xlPageField, xlColumnField
                     UpdateDic SummaryDict, pf
                  Case Else
                     ' not interested in other fields
               End Select
            End If
         Next pf
      Next pt
   Next ws
  
   If SummaryDict.Count > 0 Then ListDictionaryContents SummaryDict

End Sub
Sub UpdateDic(ByVal dic As Dictionary, ByVal pf As PivotField)
   If dic.Exists(pf.Name) Then
      dic(pf.Name) = dic(pf.Name) + 1
   Else
      dic.Add pf.Name, 1
   End If
End Sub
Sub ListDictionaryContents(dic As Dictionary)
   Dim outputSheet As Worksheet
   Set outputSheet = ActiveWorkbook.Worksheets.Add
  
   With outputSheet
      .Name = "Pivot table field usage"
      .Range("A1:B1").Value = Array("Field name", "Usage count")
      
      Dim fieldNames
      fieldNames = dic.Keys
      Dim usageCounts
      usageCounts = dic.Items
      
      .Range("A2").Resize(UBound(fieldNames) + 1).Value = Application.Transpose(fieldNames)
      .Range("B2").Resize(UBound(usageCounts) + 1).Value = Application.Transpose(usageCounts)
   End With
End Sub
Function IsValuesField(pf As PivotField) As Boolean
   IsValuesField = False
   On Error Resume Next
   IsValuesField = pf.LabelRange.address = pf.Parent.DataPivotField.LabelRange.address
End Function

and run it. It will create a new sheet with the list of field names and counts on it.
 
Back
Top