VBA - Unable to get the PivotItems property of the PivotField Class, but can hide items still?

MagicalHippo

Board Regular
Joined
Oct 13, 2015
Messages
122
Really confused here. I have a Pivot table and using VBA to hide specific fields. It works, however, when it gets to the commented code it stops and throws the "unable to get pivot field property class". I can comment out this segement of code, it will run through the 100 other Pivot Fields inside of "Cost Centre" but then freeze and throw another area for another .PivotItem field within "Cost Centre". I'm confused as to what is going on....First I thought it was due to having no fields selected, as you cannot have everything set to "False", So I set my fields to true at the beginning, but that didn't resolve anything

Code:
ws3.PivotTables("PivotTable2").PivotFields("Cost Centre #"). _
        CurrentPage = "(All)"
    With ws3.PivotTables("PivotTable2").PivotFields("Cost Centre #")
        .PivotItems("430813").Visible = True
        .PivotItems("430829").Visible = True
        .PivotItems("0").Visible = False
'        .PivotItems("182249").Visible = False
        .PivotItems("182833").Visible = False
        .PivotItems("182841").Visible = False
        .PivotItems("182895").Visible = False
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Does the value 182249 definitely exist in the pivot table ?

If it does exist, is it possible that it is the last visible entry, after all others have been set to visible=false ?
 
Upvote 0
Does the value 182249 definitely exist in the pivot table ?

If it does exist, is it possible that it is the last visible entry, after all others have been set to visible=false ?
182249 does exist in the Pivot Table, it also exist in the source Sheet. 182249 is only the 2nd visible entry out of 100. If I comment out "182249", it sets the other visible fields = False, but crashes around Pivot item 50/100.
 
Upvote 0
Sorry, I don't think I can solve this problem directly, but take a look at this thread that I started a few years ago on a similar theme - there might be something useful inside.

http://www.mrexcel.com/forum/excel-...-items-through-visual-basic-applications.html

Thanks for the link! I went through it and started using this code below, however, running into a weird issue. It selects the cases I want, however, it also selects additional Pivot Items outside of the case......which is very confusing. As I am not telling the program to do this.

Code:
Dim item As PivotItem
    
    For Each item In ws3.PivotTables("PivotTable1").PivotFields("Cost Centre #").PivotItems
    
    Select Case item.Name
    Case "430813", "430829"
    item.Visible = True
    Case Else
    item.Visible = False
    End Select
        Next item
 
Upvote 0
Is it possibly old items still stuck in the Pivot Table cache ? I think that is referred to, somewhere in that link.
 
Upvote 0
Is it possibly old items still stuck in the Pivot Table cache ? I think that is referred to, somewhere in that link.

I've tried using the Pivot Cache refresh before my loop starts, however still running into the same issue as before where the loop selects my items required, but also selects additional items...
Code:
Dim item As PivotItem
    
    ws3.PivotTables("PivotTable1").PivotCache.Refresh

    For Each item In ws3.PivotTables("PivotTable1").PivotFields("Cost Centre #").PivotItems
    
    Select Case item.Name
    Case "430813", "430829"
    item.Visible = True
    Case Else
    item.Visible = False
    End Select
        Next item
 
Upvote 0
Is it possibly old items still stuck in the Pivot Table cache ? I think that is referred to, somewhere in that link.

I found out the issue, It seems that the Pivot Table has duplicate values from the Source.....it does not make sense though, as some values are duplicated, and other values (which have duplicates are not duplicated)

any ideas as to what may cause these inconsistencies?
 
Upvote 0
Sorry, no idea without seeing the data.

No worries Gerald! I figured out how to fix it. I had to use a =Clean(Trim) function to clean up the ID's I was referencing to not allow duplications. I was wondering, If column A from A1 > A500 has IDs, is there a way to make Column B = excel formula, going down to last row cells in Column A?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top