Solved

Excel VBA getting selected pivot items from a pivot field

Posted on 2013-11-21
4
921 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
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

839 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