PivotField class error when trying to remove variable items out of a pivot chart

gruntingmonkey

Active Member
Joined
Mar 6, 2008
Messages
444
Office Version
  1. 365
Platform
  1. Windows
Having an issue where I'm trying to remove a series of numbers via a loop from a pivot table but its not working when I use a variable as opposed to a direct number/letters. I get the error "Unable to get the PivotItems property of the PivotField class" on the line that starts with ".PivotItems(....."

I'm sure I've left something really simple out but can't figure it out currently.


Code that doesnt work
VBA Code:
Sub test3()
ANum = 2226
    With Sheets("Working Out").PivotTables("PivotTable1").PivotFields("report_id")
        .PivotItems(ANum).Visible = False
    End With
End Sub

Code that does work (but only for 1 variable and I need to go through many)
VBA Code:
Sub test3()
    With Sheets("Working Out").PivotTables("PivotTable1").PivotFields("report_id")
        .PivotItems("2226").Visible = False
    End With
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Found my own answer!

Format as text and it will work with a variable

VBA Code:
ANum = Format(2226, Text)
 
Upvote 0
Solution
Except, Text won't work (unless you have Text = "@" before this line) but it is supposed to be as shown below if you'd like to format a value as text by using the Format function:

VBA Code:
ANum = Format(2226, "@")
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

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