Pivotfield question

[flipcube]

Hi All,

I have a financial table from which I created various pivot tables based on Fiscal Month. The table has columns labeled "Jan-10", "Feb-11" etc...

The Pivot Table has to show a rolling 3 months of actuals and 9 months of forecasted data. How do I make .Pivotfields dynamic so that it retrieves the correct data based on Fiscal month and year without hard coding as in the example below?


ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dec-10"), "Dec" & " " & Year(Date) - 1, xlCount

Thanks

 

[flipcube]

Sorry if the above is confusing...

here's what I am looking to do...hopefully.

below I created the variable Dec, as a string, how do i code pivotfields so that it uses the variable I created?

If FiscalMonth = "January" Then
month = "Dec"
Fyear = Year(Date) - 1
Dec = month & Fyear

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(), "Dec" & " " & Year(Date) - 1, xlCount

My source data has multiple Decembers (Dec-10, Dec-11, Dec-12 etc...). So in Fiscal Jan 2012, I would like the PT to show Dec 2011 data. if I hard code "Dec-10" into pivotfield I will always be displaying Dec 2010 data.

can this be done?

thanks for the help