Change Date on Pivot Table using VBA RRS feed

  • Question

  • I have a pivot table named “PivotTable1”. This pivot table contains, among other things, a field named “Report Month”, which is simply the month and year.  My goal is to change the value of the “Report Month” using VBA. While running the macro recorder, I changed the date on the pivot table from “May 2017” to “June 2017”. The code generated by the macro recorder is as follows…

    Sub Macro1()

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "[Report Month].[Report Month].[Year]").CurrentPageName = "[Report Month].[Report Month].[Month].&[6]&[2017]"

    End Sub

    As you can see from the above code, the macro recorder clearly indicates the date has indeed changed to “Jun 2017”, &[6]&[2017].   However, I have not been able to find a way to modify the month (i.e., the “6”) or year (i.e., the “2017”) using VBA.  For example, if I put the month and year in cells, and then try to refer to the cells in this code, I get various error message, such as:  Run-time error ‘1004’: The item could not be found in the OLAP Cube.  

    If I simply change the month in the above macro code by hand, to say "5", the pivot table refreshes to May, as expected.  So, I know the basic code is good.  I guess I just don't know the proper syntax to change the code using VBA.  

    Is there anyone out there who could help me understand how to change this date field using VBA?  I would be happy to share the pivot table with anyone, if that would help.   

    Any assistance would be greatly appreciated. Thanks.

    Monday, August 28, 2017 8:51 PM

All replies

  • Say you have a variable lMonth which is set to 5.

    Use like below

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "[Report Month].[Report Month].[Year]").CurrentPageName = "[Report Month].[Report Month].[Month]. &[" & lMonth & "]&[2017]"

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, August 29, 2017 12:02 PM