Loop Through PivotItems: Runtime Error 91
I have a dataset in a worksheet that can be different every time. I am creating a 4 from this data, but it is possible that one of the PivotItems is missing. For example:
.PivotItems("Administratie").Visible = False
If that particular value is missing from my dataset, the VBA script fails, stating that it cannot identify the element in the specified field. (error 1004)
So, I thought the loop might work. I have the following:
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim pvtItems As PivotItems
For Each pvtItem In pvtField.pvtItems
pvtItem.Visible = False
Next
But this gives me a 91 error per line for each pvtItem line:
Object variable or With block variable not set
I thought I described the variables well enough, but most likely I missed something obvious ...
source to share
I did it!: D
Dim Table As PivotTable
Dim FoundCell As Object
Dim All As Range
Dim PvI As PivotItem
Set All = Worksheets("Analyse").Range("A7:AZ10000")
Set Table = Worksheets("Analyse").PivotTables("tablename")
For Each PvI In Table.PivotFields("fieldname").PivotItems
Set FoundCell = All.Find(PvI.Name)
If FoundCell <> "itemname" Then
PvI.Visible = False
End If
Next
Woohoo
Thanks to MrExcel, the answer was there in the end, though deeply buried.
source to share
Try something like this:
Public Function Test()
On Error GoTo Test_EH
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim pvtItems As PivotItems
' Change "Pivot" to the name of the worksheet that has the pivot table.
' Change "PivotTable1" to the name of the pivot table; right-click on the
' pivot table, and select Table Options... from the context menu to get the name.
For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
Debug.Print "Pivot Field: " & pvtField.Name
For Each pvtItem In pvtField.VisibleItems
pvtItem.Visible = False
Next
Next
Exit Function
Test_EH:
Debug.Print pvtItem.Name & " error(" & Err.Number & "): " & Err.Description
Resume Next
End Function
If you want the function to simply check for the existence of a pivot item, you can use something like this:
Public Function PivotItemPresent(sName As String) As Boolean
On Error GoTo PivotItemPresent_EH
PivotItemPresent = False
For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
For Each pvtItem In pvtField.VisibleItems
If pvtItem.Name = sName Then
PivotItemPresent = True
Exit Function
End If
Next
Next
Exit Function
PivotItemPresent_EH:
Debug.Print "Error(" & Err.Number & "): " & Err.Description
Exit Function
End Function
You can call it from your code like this:
If PivotItemPresent("name_of_the_thing") Then
' Do something
End If
source to share
For Each pvtField In Worksheets("my_sheet").PivotTables("my_table").PivotFields
For Each pvtItem In pvtField.PivotItems
Debug.Print vbTab & pvtItem.Name & ".Visible = " & pvtItem.Visible
/*.PivotItems(pvtItem).Visible = False*/
Next
Next
.PivotItems("certain_Item").Visible = True
It doesn't work ... all the variables are still visible. The error is not displayed, it compiles, but the values still exist.
The commented line I added had my own "invention" but it is not valid.
Edit: Quick question: can I use an IF statement to check if a particular PivotItem is actually in the pivot data? Something like
If PivotItem("name_of_the_thing") = present Then {
do_something()
}
source to share
You cannot say .PivotItems(pvtItem).Visible
"outside the block With
". Let's say " pvtField.PivotItems(pvtItem.Name).Visible = False
".
I also edited my original answer to include error handling when Excel cannot set the Visible property. This is because the pivot data table requires at least one row field, one column field, and one data item, so the last of each cannot be invisible.
You will also receive a 1004 error when trying to access an element that is no longer visible; I think you can ignore them.
source to share
The error is thrown at the end of the loop.
I have combined both answers from Patrick to the following:
With ActiveSheet.PivotTables("Table").PivotFields("Field")
Dim pvtField As Excel.PivotField
Dim pvtItem As Excel.PivotItem
Dim pvtItems As Excel.PivotItems
For Each pvtField In Worksheets("Sheet").PivotTables("Table").PivotFields
For Each pvtItem In pvtField.PivotItems
If pvtItem.Name = "ItemTitle" Then
pvtField.PivotItems("ItemTitle").Visible = True
Else
pvtField.PivotItems(pvtItem.Name).Visible = False
End If
Next
Next
End With
If an element matches a specific string, that element is set to True. Else; Set of False items. If False, the condition is thrown.
I know there is only one match for the True state. Although when I 'F8' step through the macro, the True condition is never entered ...
And that explains the error, everything is set to False. (thanks Patrick!)
Leads me to the question ... what is a PivotItem?
The idea behind the thing:
It solves (or should solve) the following: a variable-sized dataset where one column is of interest for that particular table. From this column, I need to calculate the value and put it in the table. There are some conditions for the table and a combination with another column is required, so PivotTable is the best solution.
The problem is that some datasets do not show one specific value. The values displayed by DO are different each time.
source to share
PivotItems are individual values in a field (column, row, data). I think of them as "buckets" that contain all the individual pieces of data that you want to combine.
Instead of going through all the fields of the pivot table (column, row, and data), you can simply view the fields of interest. For example, this code will only show the specified anchor elements for the specified field
Public Sub ShowInPivot(Field As String, Item As String)
On Error GoTo ShowInPivot_EH
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim pvtItems As PivotItems
For Each pvtItem In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields(Field).PivotItems
If pvtItem.Name = Item Then
pvtItem.Visible = True
Else
pvtItem.Visible = False
End If
Next
Exit Sub
ShowInPivot_EH:
Debug.Print "Error(" & Err.Number & "): " & Err.Description
Exit Sub
End Sub
Let's say I have a pivot table showing the number of releases per client release and where they were found in our SDLC. Customer and Issue are column fields and Phase is a row field. If I wanted to restrict the pivot table to issue count for CustomerA, Release 1.2 during QA, I could use the sub above like this:
ShowInPivot "Customer", "CustomerA"
ShowInPivot "Release", "1.2"
ShowInPivot "Phase", "QA"
source to share
I also had an error when trying to set pivotitem visible to true and false .. this worked earlier, but no longer worked ... I was comparing two values and did not explicitly change the string in integer for comparison .. doing this made the error disappear ..
.. so if you get this message, check if any values are compared to make the item visible or not, otherwise pivotitem is null and cannot make it visible or not.
I had an error that said, "Unable to set visible property of collapse element class" on this line:
For Each pi In pt.PivotFields("Fecha").PivotItems
If pi.Name = ffan Then
pi.Visible = True
Else
pi.Visible = False '<------------------------
End If
Next pi
Then I read online that I had to sort the manual and then clear the cache. I did this, but the error still popped up ..... then I read that it was because I had a date in that pivot field, so I change it to my number to the general number and then to the date when i want to set visibility, i change it to total number too. that's not a problem !!!!!!!!!!!!!! .... here it is .... I hope this can be helpful because I was desperate !!!
Dim an As Variant
an = UserForm8.Label1.Caption 'this label contains the date i want to see its the pivot item i want to see of my pivot fiel that is "Date"
Dim fan
fan = Format(an, "d m yyyy")
Dim ffan
ffan = Format(fan, "general number")
Sheets("Datos refrigerante").Activate 'this is the sheet that has the data of the pivottable
Dim rango1 As Range
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Set rango1 = Selection
ActiveSheet.Cells(1, 1).Select
rango1.Select
Selection.NumberFormat = "General" 'I change the format of the column that has all my dates
'clear the cache
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc
'now select the pivot item i want
Dim pi As PivotItem
Set pt = Sheets("TD Refrigerante").PivotTables("PivotTable2")
'Sets Pivot Table to Manual Sort so you can manipulate PivotItems in PivotField
pt.PivotFields("Fecha").AutoSort xlManual, "Fecha"
'Speeds up code dramatically
pt.ManualUpdate = True
For Each pi In pt.PivotFields("Fecha").PivotItems
If pi.Name = ffan Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
pt.ManualUpdate = False
pt.PivotFields("Fecha").AutoSort xlAscending, "Fecha"