Pivot Table Page Fields ~ Filtering for multiple values

J

Juan Correa

Hello..

I've been working on a bit of code that genrates a number of Pivot Tables
for my bosses based on a large raw data spreadsheet. So far I have the macro
working like a charm, but now I'm trying to get fancy (or maybe just simplify
the code a bit).

One segment of the code generates a PT with this layout:

' Set a Pivot Table variable to our new Pivot Table
Set PSPT = ActiveSheet.PivotTables("PSPivot")


' Set the layout of the Pivot Table
PSPT.AddFields RowFields:=Array("Forecast Status Description-Current",
"Country Name"), _
ColumnFields:="Booked Month", PageFields:=Array("Year", "PSCol")
PSPT.PivotFields("Forecast Status
Description-Current").PivotItems("UPSIDE").Position = 2
PSPT.PivotFields("Year").CurrentPage = "2009"
PSPT.PivotFields("PSCol").CurrentPage = "PS"
PSPT.NullString = "0"
Cells.EntireColumn.AutoFit
ActiveWorkbook.ShowPivotTableFieldList = False

The Page Field "PSCol" comes from a column created at a previous step in the
macro. This Column takes the values (text) from another column in the raw
data and calculates this formula: =Left(rawdatacell, 2)

The information in the raw data column looks like this (There are over 1000
different descriptions):

PS Application Development
PS Business Consulting
PS Customer Education
PS Deployment
PS Implementation
PS Integration
PS IT Consulting
PS Project Management
PS Software Maintenance & Sup
SCER
Specialty Media
SW- INTERNATIONAL DOCUMENT ENT
SW- NCR AIRLINE MOBILE WEB
SW- NCR HOTEL CHECK-IN

The reason I created the PSCol with that formula in it is so that I can then
filter in the Pivot Table with the expression:

PSPT.PivotFields("PSCol").CurrentPage = "PS"

My question is:
Is there a way where I could avoid creating that PSCol and use the raw data
column instead? I would need to filter that page field to only show the
following descriptions:
PS Application Development
PS Business Consulting
PS Customer Education
PS Deployment
PS Implementation
PS Integration
PS IT Consulting
PS Project Management
PS Software Maintenance & Sup

Being that they all start with PS, I thought there could be a way of using
the Like operator, but nothing I try works.

Any help would be appreciated.

juan correa
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top