Pivot Item selection error

**RaydenUK** · 09-24-2015, 01:41 PM

I am getting an error when trying to set the the pivot item to a range. The range ("SK5") would include a name like "Tom Smith", which i would like to be the only option chosen for the filter. I am getting an "Unable to get the PivotItems Property of the PivotField Class" error when i deactivate the "On Error Resume Next" line. I know the name "Tom Smith" exists as a pivotitem, just not sure where the error is coming from.

Dim dbmitm As Range
Dim pvt as PivotTable

Set DBM = Sheet1.Range("SK5")
Set pvt = ActiveSheet.PivotTables("PivotTable21")
On Error Resume Next
 With pvt21.PivotFields("DBM")
        .ClearAllFilters
        .PivotItems(dbmitm).Visible = True
 End With

**humdingaling** · 09-24-2015, 09:20 PM

you set DBM but dim dbmitm

where is dbmitm value coming from then?

**humdingaling** · 09-24-2015, 09:32 PM

also if you clear filters and only make 1 visible...well then...all items are still visible

if you want only one item to be visible you need to make every other item not visible

**humdingaling** · 09-24-2015, 09:45 PM

i see your profile says 2013...if your only wanting one item from one pivot
you may be better off using pivotfield/pagefield
http://www.thespreadsheetguru.com/bl...l-pivot-tables
see section Report Filter On A Single Item

however if you still want to use pivot items
see section Report Filter On Multiple Items of the website

if you want to learn how to loop the pivot items instead of setting one by one
see post #2 on this thread to see how to i use set Pivot items syntax
its pretty similar to what your asking however i am doing multiple pivot tables with one cell
http://www.excelforum.com/excel-prog...ot-tables.html

**RaydenUK** · 09-25-2015, 02:04 PM

I meant to set dbmitm instead of DBM, thank s for catching that, but of course, by clearing all the filters then everything is visible. My logic was completely backwards! Thank you for the help!

**humdingaling** · 09-27-2015, 08:16 PM

not a problem
after taking in the information i have given and putting it into practice if you still having issues please come back and let me know what they are ill see if i can still help
otherwise if that takes care of the original question

please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Pivot Item selection error

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pivot Item selection error

Re: Pivot Item selection error

Re: Pivot Item selection error

Re: Pivot Item selection error

Re: Pivot Item selection error

Re: Pivot Item selection error

Thread Information

Users Browsing this Thread

Similar Threads

Pivot Table Filter Not Allowing Item Selection.

"Cannot group that selection" error in pivot table work-around

[SOLVED] Pivot Table Calculated Item Error

Excel 2007 - Pivot Table: “Cannot Group That Selection” error!!

[SOLVED] Pivot Table Error: 'CANNOT GROUP THAT SELECTION'

Multi select Listbox Items selection based on other Listbox item selection.

[SOLVED] Pivot Item: removing non-existant item from the drop down

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions