• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

Excel VBA getting selected pivot items from a pivot field

I have the following code that attempts to loop through the selections on a pivot table field and returns a string with the selected items.   It works as long as only 1 item is selected but if the multiple item flag is set and two or more selections are made, all the items return visible = false.  Is there another property or do I need to do something with the page orientation?

Here is the code:
Function PTfilters(fldName As String) As String


Dim PT As Excel.PivotTable
Set PT = ThisWorkbook.Sheets("MySheet").PivotTables("myPivotTable")

Dim piFilter As PivotItem
Dim iFilterCount As Integer

   iFilterCount = 0
   
   For Each piFilter In PT.PivotFields(fldName).PivotItems
         If piFilter.Visible Then
         iFilterCount = iFilterCount + 1
         PTfilters = PTfilters & "," & piFilter.Name
        End If
   Next piFilter
   PTfilters = Mid$(PTfilters, 2)

End Function
0
dond123
Asked:
dond123
  • 3
1 Solution
 
thydzikCommented:
can you please attach an example workbook.
0
 
dond123Author Commented:
Unfortunately as the pivot table has an external data source I am unable to attach the workbook.
0
 
dond123Author Commented:
Removing and reinserting a new pivot table solved the issue.
0
 
dond123Author Commented:
Same code worked after removing and inserting new pivot table.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now