Excel/Pivot Table Automation
QUESTION: Hi Gulshan,
I am trying to automate Pivot table filtering with VBA
How can I select a specific item in the Report Filter list with VBA?
The ordinary selection for row or column labels does not work:
Set oPi = ActiveSheet.PivotTables("PivotTable2").PivotFields("OwnerName").PivotItems(sPiName)
If oPi Is Nothing Then
MsgBox sPiName & " not found."
For Each oPi In ActiveSheet.PivotTables("PivotTable2").PivotFields("OwnerName").PivotItems
If oPi.Value <> sPiName Then
oPi.Visible = False
VBA is not looking for the item sPiName in the Report Filter...
ANSWER: Hi Ulrich,
I'm not sure whether sPiName is a variable or is it the exact value you are looking for.
If the value you are looking for is sPiName, then try enclosing it in quotes ("sPiName")
---------- FOLLOW-UP ----------
QUESTION: Hi Gulshan, unfortunately sPiName it is not an exact value (string).
sPIName is taken from a list of name items. The code extract I have sent to you is imbedded in another loop looping through all name objects sPiName
The problem is that the the pivot fields "Ownername" are not in a column or row of the pivot table but selected as report filter ;-)
So what to do in order to point to the Report Filter members "Ownername"?
The extract of code you have sent should work. I can't see a problem there, I think I would need to see the complete code and file if possible.
I can give a better answer if you could send me the file (please replace any confidential data with dummy data) - firstname.lastname@example.org
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here