[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel VBA getting selected pivot items from a pivot field

Posted on 2013-11-21
4
Medium Priority
?
1,113 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

649 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