?
Solved

Excel VBA getting selected pivot items from a pivot field

Posted on 2013-11-21
4
Medium Priority
?
1,061 Views
Last Modified: 2013-12-20
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
Comment
Question by:dond123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 11

Expert Comment

by:thydzik
ID: 39679560
can you please attach an example workbook.
0
 

Author Comment

by:dond123
ID: 39712426
Unfortunately as the pivot table has an external data source I am unable to attach the workbook.
0
 

Accepted Solution

by:
dond123 earned 0 total points
ID: 39720150
Removing and reinserting a new pivot table solved the issue.
0
 

Author Closing Comment

by:dond123
ID: 39731380
Same code worked after removing and inserting new pivot table.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Gain an elementary understanding of Blockchain technology.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question