Solved

Excel VBA getting selected pivot items from a pivot field

Posted on 2013-11-21
4
852 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
Comment Utility
can you please attach an example workbook.
0
 

Author Comment

by:dond123
Comment Utility
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
Comment Utility
Removing and reinserting a new pivot table solved the issue.
0
 

Author Closing Comment

by:dond123
Comment Utility
Same code worked after removing and inserting new pivot table.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now