Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Page Fields ~ Filtering for multiple values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Multiple Page Fields - AND/OR Logic Choice? | Excel Discussion (Misc queries) | |||
Use multiple page fields in pivot table | Excel Worksheet Functions | |||
Macro on filtering pivot table (pivot fields) = debug | Excel Programming | |||
Pivot table page fields add values previously deslected | Excel Discussion (Misc queries) | |||
Filtering or linking Page Fields in an Excel Pivot Table | Excel Programming |