Hi,

I have to report on two aspects: at "Area" & "Region" level depending upon what the user wants to view. So I have a cell (M2) where the option is provided to either select "All Area" or Specific Area as an option. Depending upon the selection, my columns should represent the values i.e. if I select "All Area" as my option then "Area" field from my power pivot data should be in the column filter. If I select a specific Area, the Area field should be in the Report filter with the filter set to the selected Area and then the column should have the Region values corresponding to that Area. I have a master table created for the purpose and this is also used for other powerpivot tables.

I wanted to write a macro code on the cell (M2) such that basis my selection, the powerpivot structure should change. I tried the below code, but it doesn't work.

I am very new to macro and unfortunately do not have access to anybody who is an expert in VBA for excel. Spent almost a day searching google for proper syntax but it seems there is very little available for OLAP based Pivot tables. Can some help me with the correct piece of code. Below is what I have written:

Dim FilterValue As String

FilterValue = Sheets("Dashboard").Range("m2").Value

'Dashboard is the worksheet and AreaRegionColumn is the name of the Pivot table
With Sheets("Dashboard").PivotTables("AreaRegionColumn").ClearAllFilters - Not able to reset the fields from Report filter and Column filter.
End With

If FilterValue = "Global" Then
ActiveSheet.PivotTables("AreaRegionColumn").CubeFields(72). _
EnableMultiplePageItems = True
With ActiveSheet.PivotTables("AreaRegionColumn")
With .PivotFields("[Region].[Area].[Area]")
'.Orientation = xlPageField - This is where it gets stuck, also I am not able to remove the filters and set Area as the column
End With
End With

Else
'This piece is working fine because I have already setup the pivot table with Region as my column and Area as Report filter and this is able to apply the filter based on the value in cell (M2)
ActiveSheet.PivotTables("AreaRegionColumn").CubeFields(72). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("AreaRegionColumn").PivotFields( _
"[Region].[Area].[Area]").VisibleItemsList = Array( _
"[Region].[Area].&[" & FilterValue & "]")
End If
End Sub