You are here:

Excel/Pivot Table Automation

Advertisement


Question
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."
   Else
       For Each oPi In ActiveSheet.PivotTables("PivotTable2").PivotFields("OwnerName").PivotItems
         If oPi.Value <> sPiName Then
         oPi.Visible = False
         End If
       Next
++++++++++++++++++++++++++++++++++++++++++++++++++++
VBA is not looking for the item sPiName in the Report Filter...

BR

Ulrich

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")

Gulshan.

---------- 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"?

BR

Ulrich

Answer
Hello Ulrich,

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) - gulshanrajpurswani@gmail.com

Gulshan.
About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.