Page filter in Pivot table - any alternative to macro

[jpfernan]

I have different views in dashboard controlled by Options button (Forms). I have a macro which will then considers the option selected and applies the page filter in Pivot table.


For example, Option FS will show Page filter named "FS" in Pivot table likewise ,Option Total TECH will show "(All)"


The macro is as follows :


If Range("D1") = "Total TECH" Then

ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = _

"(All)"

End If

If Range("D1") = "FS" Then

ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = _

"FS"

End If

If Range("D1") = "PS" Then

ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = _

"PS"

End If

If Range("D1") = "MRD" Then

ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = _

"MRD"

End If

If Range("D1") = "CMUT" Then

ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = _

"CMUT"

End If


The issue is that the dashboard needs to be sent to Executive VPs and I cannot ask them to click on Enable Macro option every time


I just want to know if there is any other alternative to Macro. The complication is that the Pivot table has many row labels which needs to be shown in the dashboard.

 

[Hui]

You can provide a Drop Down list and then have the macro work of that automatically.

A drop Down will display a list of the options

and return a Index Number to a cell

In Your case if you linked the Drop Down it to cell D1


I would also change your code as below

[pre]

Select Case Range("D1").value
Case 1
ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = "(All)"
Case 2
ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = "FS"
Case 3
ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = "PS"
Case 4
ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = "MRD"
Case 5
ActiveSheet.PivotTables("PivotTable2").PivotFields("Industry").CurrentPage = "CMUT"
End Select

[/pre]

 

[jpfernan]

Thanks Hui. But my question is there anyway to get rid of the macro and build some formula?

 

[Hui]

No

There's no formula link to the pivot chart controls

But the above method will be automatic and seamless after they have enabled macros on opening the file