VBA filter Pivot table based on a specific cell value

[clomah]

Hello!

Hoping someone can help me as I've spent the past 5 hours trying different methods with no luck.

I've recorded the below macro and it works, however I'm trying to replace [2022-05-01T00:00:00] and [2023-05-01T00:00:00] with a reference to cells A1 and A2 (based on the month selected)

Sub PivotMonth()

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[XXX Calendar].[XXX Fiscal Month].[XXX Fiscal Month]").VisibleItemsList = _
        Array("[XXX Calendar].[XXX Fiscal Month].&[2022-05-01T00:00:00]", _
        "[XXX Calendar].[XXX Fiscal Month].&[2023-05-01T00:00:00]")
End Sub

 

[Domenic]

What values do A1 and A2 contain?

 

[clomah]

Month!

I'm guessing it would have to be in that same format? So I'm having a formula to translate to:

[2022-06-01T00:00:00] A1
[2023-06-01T00:00:00] A2

If that makes sense.

Thank you!

 

[Domenic]

Does it contain a value like this...

6

or

Jun

or

June

???

 

[clomah]

Does it contain a value like this...

6

or

Jun

or

June

???

Jun

 

[Domenic]

In that case, try the following...

Array("[XXX Calendar].[XXX Fiscal Month].&[2022-" & Format(Range("A1").Value & " 1", "mm") & "-01T00:00:00]", _
        "[XXX Calendar].[XXX Fiscal Month].&[2023-" & Format(Range("A2").Value & " 1", "mm") & "-01T00:00:00]")

Hope this helps!