PDA

View Full Version : Probelm Filtering Pivot Table



keepers123
05-29-2013, 07:43 AM
I wrote the following code, but it gives me an error

This code works:


Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Time].[Time Hierarchy].[FISCAL MONTH]").VisibleItemsList = Array("[Time].[Time Hierarchy].[FISCAL MONTH].&[2013]&[4]")

but i wanted it to be more general so here are the changes i made


Dim CurrentMonth As Integer
Dim CurrentYear As Integer

CurrentMonth = Month(Date) - 1
CurrentYear = Year(Date)
Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Time].[Time Hierarchy].[FISCAL MONTH]").VisibleItemsList = Array("[Time].[Time Hierarchy].[FISCAL MONTH].&[CurrentYear]&[CurrentMonth]")

The error says is cannot find it in the database.

Also i want to do the same thing with respect to an array


Dim arr2() As String
Dim arrelement As Integer

For arrelement = 0 To 5
arr2() = Split("QRO,MRO,FVO,WMO,XHO,JCO", ",")
Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Site].[Site].[Site]").CurrentPageName = "[Site].[Site].&[arr2(arrelement)]"
Next arrelement

but this wouldn't work either