If I'm understanding this correctly, it looks like you're trying to use the
results of the curMonth function (which is a string) and wanting it to
execute like it was code. There may be some way to do that, but why don't
you just have curMonth return just the string:
currMonth = "[PERIOD].[All PERIOD]" & "." & Y &
"." & Q & "." & m & Chr(34)
End Function

And then down in the rest of your code call the .AddPageItem method with the
string returned from curMonth:

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]"
.AddPageItem Module5.currMonth
End With

-Erik

"OrrLyfe" wrote in message
...
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