Hi,

I have a pivot table and I'm tryng to change the page data dynamically. The
page data is a period dimension and based on the quarter, and month, it
should filter the page area...here is my code below and I will explain where
the
problem is below...

This is a custom function that gets the quarter...nothing wrong here
Function QTRNum(ENTER_DATE)
QTRNum = DatePart("q", ENTER_DATE)
End Function
-----------------------------------------------------------
This is the function that does the work.

Function currMonth()
'Declare some stuff
Dim D As String
Dim Y As String
Dim m As String
Dim Q As String

'Set values. I'm palcing the format of the data for the for the page field

Q = "[Quarter " & Module5.QTRNum(Date) & "]"
Y = "[" & Year(Date) & "]"
m = "[" & MonthName(Month(Date)) & "]"

'This simply assigns the .addpageitem to the currMonth variable. I know the
value is correct, because I will debug.pring CurrMonth and then paste the
results in the procedure and it executes fine. It's like it won't take a
string for this method.

It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter
3].[October]"

currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y &
"." & Q & "." & m & Chr(34)
End Function
---Now, this is what should set the filter

With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]")
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]"
.AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]"
Module5.currMonth
End With

HELP PLEASE!!!!!!!!!!!!



Expand AllCollapse All

Manage Your Profile