Change Pivot Table Date using VBA

[george korodan]

I have a pivot table (which I did not create) named “PivotTable1”. This pivot table contains, among other things, a field named “Report Month”. The “Report Month” field appears to be a concatenation of two other fields: “Month” and “Year”. My goal is to change the value of “Report Month” using VBA. Using the macro recorder I created “Macro(1)”, by changing the date from “May 2017” to “June 2017”. The results of the macro recorder are 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 date is indeed “Jun 2017” (&[6]&[2017]). And this little bit of code runs perfectly – as is. However, if I attempt to change the “Month” from “6” or the “Year” from “2017”, using VBA, I get various error messages.
_________________________________________________________________________________________
For example, if I make “Month” and “Year” variables, like this…

Sub Macro2()
Dim aaa, bbb As Variant
aaa = 7
bbb = 2017
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Report Month].[Report Month].[Year]").CurrentPageName = _
"[Report Month].[Report Month].[Month].&[aaa]&[bbb]"
End Sub

I get the following error message. Run-time error ‘1004’: The item could not be found in the OLAP Cube.
_________________________________________________________________________________________
And if I make the “Month” and “Year” refer to range.values, like this…

Sub Macro3()
Dim aaa, bbb As Range
Set aaa = Range("A1")
Set bbb = Range("B1")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Report Month].[Report Month].[Year]").CurrentPageName = _
"[Report Month].[Report Month].[Month].&aaa.value&bbb.value"
End Sub

I get the following error message: Run-time error ‘1004’: XML for Analysis parser: The restriction value provided by the consumer either does not match other restrictions or refers to an unknown object.
__________________________________________________________________________________________
Is there anyone out there who could help me understand how to change this date field (i.e., “Report Month”) dynamically, using VBA?
I would be happy to share the pivot table with anyone, if that would help.
Any assistance would be greatly appreciated. Thanks.