I'm working on a big reporting system with a lot of Pivot Tables, Pivot Charts, Slicers and Filters.
So to be sure that all the Pivot Tables have the right sources and which slicers apply to each one of them, I started to work on a code that aggregate useful info for each Pivot Table :
Sub Test_2_Pt_Report_by_sheet() ThisWorkbook.Save Application.ScreenUpdating = False Dim pT As PivotTable, _ Sl As Slicer, _ RWs As Worksheet, _ Ws As Worksheet, _ pF As PivotFilter, _ pFL As PivotField, _ HeaDers As String, _ TpStr As String, _ Sp() As String, _ A() ReDim A(20, 0) Set RWs = ThisWorkbook.Sheets("PT_Report") HeaDers = "Name/Sheet/Address/Version/Source/SlicerCache/Refreshed/Slicer_Number/Slicers/Slicers_Values" & _ "ActiveFilters/Filters/ActiveValues/HasChart/Chart_Location/ / / / / / " For i = LBound(A, 1) To UBound(A, 1) A(i, 0) = Split(HeaDers, "/")(i) Next i On Error Resume Next For Each Ws In ThisWorkbook.Sheets For Each pT In Ws.PivotTables TpStr = vbNullString ReDim Preserve A(UBound(A, 1), UBound(A, 2) + 1) With pT A(0, UBound(A, 2)) = .Name A(1, UBound(A, 2)) = Ws.Name A(2, UBound(A, 2)) = Replace(.TableRange2.Address & " / " & .TableRange1.Address, "$", "") A(3, UBound(A, 2)) = .Version A(4, UBound(A, 2)) = .SourceData A(5, UBound(A, 2)) = "" '.PivotCache.Name A(6, UBound(A, 2)) = .RefreshDate A(7, UBound(A, 2)) = .Slicers.Count For Each Sl In .Slicers TpStr = TpStr & "/" & Sl.Name '& " : " & Sl.Shape.Parent.Name Next Sl If Len(TpStr) > 0 Then A(8, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1) TpStr = vbNullString Sp = Split(A(8, UBound(A, 2)), "/") For i = LBound(Sp) To UBound(Sp) TpStr = TpStr & "/" & GetSelectedSlicerItems(Sp(i)) Next i If Len(TpStr) > 0 Then A(9, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1) If .Version = xlPivotTableVersion12 Then TpStr = vbNullString For Each pF In .ActiveFilters TpStr = TpStr & "/" & pF.PivotField.Name Next pF If Len(TpStr) > 0 Then A(10, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1) Else End If TpStr = vbNullString For Each pFL In .DataFields TpStr = TpStr & "/" & pFL.Name Next pFL If Len(TpStr) > 0 Then A(11, UBound(A, 2)) = Right(TpStr, Len(TpStr) - 1) 'A(12, UBound(A, 2)) = .VisibleFields 'A(13, UBound(A, 2)) = ' A(14, UBound(A, 2)) = ' A(15, UBound(A, 2)) = ' A(16, UBound(A, 2)) = ' A(17, UBound(A, 2)) = ' A(18, UBound(A, 2)) = .PivotChart.HasChart ' A(19, UBound(A, 2)) = .PivotChart.Chart.Shapes.Name ' A(20, UBound(A, 2)) = End With Next pT Next Ws RWs.Cells.ClearContents RWs.Cells.ClearFormats RWs.Range("A1").Resize(UBound(A, 2) + 1, UBound(A, 1) + 1).Value = Application.Transpose(A) RWs.Columns("A:Z").EntireColumn.AutoFit RWs.Activate Set Ws = Nothing Set RWs = Nothing Application.ScreenUpdating = True MsgBox "done" End Sub
And the function to get selected items in a slicer :
Public Function GetSelectedSlicerItems(SlicerName As String) As String Dim oSc As SlicerCache Dim oSi As SlicerItem Dim lCt As Long Application.Volatile On Error Resume Next Set oSc = ThisWorkbook.SlicerCaches("Slicer_" & Replace(SlicerName, " ", "")) If Not oSc Is Nothing Then For Each oSi In oSc.SlicerItems If oSi.Selected Then GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", " lCt = lCt + 1 ElseIf oSi.HasData = False Then lCt = lCt + 1 End If Next If Len(GetSelectedSlicerItems) > 0 Then If lCt = oSc.SlicerItems.Count Then GetSelectedSlicerItems = "All Items" Else GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) End If Else GetSelectedSlicerItems = "No items selected" End If Else GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found" End If End Function
Sl.Shape.Parent.Name only works when the Slicer is on the same sheet as the Pivot Table. And I can't seem to locate it more accurately than on a sheet (not dramatic).
When I use
pT.Parent.Name, I get the sheet's name, but I want the SlicerCache's name. (maybe I could loop on SlicerCaches rather than Sheets, and use one of these expressions to get the sheet name)
I struggle to work with the Pivot Charts, as the property
HasChart is already in the Pivot Chart object... I wanted to know if there was one, where it is and how is it named. I thought of a function with error handling to avoid breaks but I'm not sure it is the best way.
ActiveFilters and Pivot Table version
ActiveFilters, I get this error message for some tables:
This Pivot Table was created in a later version of Excel and can't be updated in this version.
I created few Pivot Tables in Excel 2013 and usually work on 2010, I tried to filter with the version, but they all have the same :
xlPivotTableVersion14 (value = 4), except one that give 5 which hasn't any constant to describe it... EDIT : On Excel 2013, I found this :
Const xlPivotTableVersion15 = 5
So, any enlightenment, advice or workaround are welcome!