(VBA) OLAP Pivot Table - Possible to add an inputbox?

[GSanchez]

Hi Everyone,

I have a workbook that contains a lot of OLAP pivot tables and so the goal for me is to create a macro that will automatically change the filter known as "month year" for all of the pivot tables in this workbook. The good news is that the macro I wrote manages to do that, but now I am trying to figure out a way to add an inputbox for other users who can type the month & year rather than going inside the VBA Code and adjusting the code.

Any help or clue is appreciated!

Sub Testing_Filter_Changes()

Dim ws As Worksheet
Dim changemonth As String

changemonth = InputBox(Prompt:="Month and Year?")

For Each ws In ThisWorkbook.Worksheets

ws.PivotTables("PivotTableGPR").PivotFields( "[Calendar].[Month Year].[Month Year]").CurrentPageName = _
            "[Calendar].[Month Year].&[changemonth]"

Next ws

End Sub

 

[mrshl9898]

Perhaps:

 "[Calendar].[Month Year].[" & changemonth & "]"

 

[GSanchez]

Perhaps:

 "[Calendar].[Month Year].[" & changemonth & "]"

So close! It seems to accept whatever I type in the inputbox, but then it says

"Item could not be found in the OLAP Cube."

Which is weird because I am typing "Apr 2021" and this is definitely found in the OLAP Cube as I wrote it in the VBA code before.

Not sure why it is not accepting it from the inputbox.

 

[mrshl9898]

Did you record the macro when filtering to check the format?

My work OLAP selection is "2008-01 January" but I need to write it as "[Actual Delivery Date].[Month].&[2008}&{1]"

Data validation may be needed if other users are typing in whatever, or at least a message telling them the format.

 

[GSanchez]

I did record the macro when filtering, here were the results:

ActiveSheet.PivotTables("PivotTableGPR").PivotFields( _

 "[Calendar].[Month Year].[Month Year]").CurrentPageName = _

 "[Calendar].[Month Year].&[Apr 2021]"

 

[mrshl9898]

I think I see it, my fault there sorry.

 "[Calendar].[Month Year].&[" & changemonth & "]"

 

[GSanchez]

Thank you so much! This works!

I am definitely going to research more about this.

By any chance can you explain why [" & changemonth & "] allows the inputbox to be used?

 

[mrshl9898]

"making some kind of string"

=

"making some " & "kind of " & "string"

pretty much like concatenating, if that makes sense

 

[GSanchez]

That makes sense! I appreciate you taking the time to write that for me.

Thanks again!

 

[mrshl9898]

No problems, happy to help