VBA Pre-Filtter PowerPivot Table RowField before Displaying in Pivot Table

[dmarq]

Greetings,

Based on end user requirements, I have created a VBA based Slicer that allows the user to choose the row field, rather than filter within a data field. I used this YT video as the basis of my code.

https://www.youtube.com/watch?v=nmAdW8-3E2I&t=285s

I was able to successfully implement this slicer, however, I also needed to introduce some additional parameters, of which one of them was to ensure the Pivot Table did not exceed more than 10 rows for a (Top 10 of item) as other pivot tables will be used in the same worksheet and would overlap and generate the dreaded 'A PivotTable cannot overlap another PivotTable report' error.

I used a Macro recorder to see the VBA code on how to add a filter to a PivotField before adding it to the PivotTable

(comments are mine)

Sub Macro2()
'
' Macro2 Macro
'

'
' Code to remove the view of previous xlRow Field

ActiveSheet.PivotTables("pvtHierVol").CubeFields( _
"[ReportHierarchy].[TEAM LEADER]").Orientation = xlHidden

' Method that enables users to apply a filter to PivotFields not yet added to the PivotTable by
' creating the corresponding PivotFieldobject

ActiveSheet.PivotTables("pvtHierVol").CubeFields( _
"[ReportHierarchy].[COLLECTOR]").CreatePivotFields

' Subsequent action to apply the 'Top 10 of Item' to the PivotTable

ActiveSheet.PivotTables("pvtHierVol").PivotFields( _
"[ReportHierarchy].[COLLECTOR].[COLLECTOR]").PivotFilters.Add2 Type:= _
xlTopCount, DataField:=ActiveSheet.PivotTables("pvtHierVol").CubeFields( _
"[Measures].[Sum of Outstanding_Amt(USD)]"), Value1:=10

' Final method to apply to insert the xlRowField
With ActiveSheet.PivotTables("pvtHierVol").CubeFields( _
"[ReportHierarchy].[COLLECTOR]")
.Orientation = xlRowField
.Position = 1
End With

End Sub



When I introduce the same code into my VBA, it appears the CreatePivotFields does not work or is not viewable, because i get a Run Time error '1004' - Unable to get the PivotFields property.

Below is my current pseudo code and where I get the error when I step-into the VBA code:

-------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Selection")) Is Nothing Then

Dim Pvt As PivotTable
Dim Cf As CubeField
Dim Sh As Worksheet


Set Sh = ActiveSheet
Set Pvt = Sh.PivotTables("pvtHierVol")

If Range("Selection") = Range("Hierarchy.Type") Then
Exit Sub
Else

Select Case Range("Selection.Number")

Case 1
Pvt.CubeFields("[ReportHierarchy].[TEAM LEADER]").Orientation = xlHidden
Pvt.CubeFields("[ReportHierarchy].[COM]").Orientation = xlHidden

Pvt.ClearAllFilters

Pvt.CubeFields("[ReportHierarchy].[COLLECTOR]").CreatePivotFields

Pvt.PivotFields( _
"[ReportHierarchy].[COLLECTOR].[COLLECTOR]").PivotFilters.Add2 Type:= _
xlTopCount, DataField:=ActiveSheet.PivotTables("pvtHierVol").CubeFields( _
"[Measures].[Sum of Outstanding_Amt(USD)]"), Value1:=10

With Sh.PivotTables("pvtHierVol").CubeFields( _
"[ReportHierarchy].[COLLECTOR]")
.Orientation = xlRowField
.Position = 1
End With


Exit Sub

Case 2

...

End Select

End If

End If

End Sub

----------------------------------------------

I feel I need to Set the PivotField prior to the Case Statement, but I am not 100%, nor on how I would do that.

Appreciate any help, pointers, or feedback.

Thanks!