Goal to set a report filter in a pivot table based on a referenced cell (data source if OLAP)
I am struggling to get the syntax correct.. I have tried several different approaches.
I am familiar with how to set pivotitems in a pivot filter in regular pivot tables but currentpage property doesn't work. I have found that visiblelistitems is appropriate for OLAP sources but can't get it to work.
See code
Any assistance would be greatly appreciated . I must update a number of sheets and pivottables for reports next week and do not want to have to filter each manually
I am struggling to get the syntax correct.. I have tried several different approaches.
I am familiar with how to set pivotitems in a pivot filter in regular pivot tables but currentpage property doesn't work. I have found that visiblelistitems is appropriate for OLAP sources but can't get it to work.
See code
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'E1 or E2 is touched
If Intersect(Target, Worksheets("STatus PP").Range("E1:E2")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim PT As PivotTable
Dim Field As PivotField
Dim NewCat As String
Dim pvtfld As PivotField
Dim pi As PivotItem
'Here you amend to suit your data
Set PT = Worksheets("STatus PP").PivotTables("PivotTable2")
Set Field = PT.PivotFields("[MASTER].[Reporting Period].[Reporting Period]")
'NewCat = "[MASTER].[Reporting Period].&[" & Range("E1").Text & "]" ' different approach didn't work
NewCat = Worksheets("STatus PP").Range("E1").Value
'This updates and refreshes the PIVOT table
With PT
Field.ClearAllFilters
' Field.PivotFilters.Add Type:=xlCaptionContains, Value1:=NewCat ' doesn't work
For Each pvtfld In PT.PivotFields
If pvtfld.Name = "[MASTER].[Reporting Period].[Reporting Period]" Then
Dim myArray() As Variant
Dim myR As Range
Set myR = Worksheets("STatus pp").Range("E1")
ReDim myArray(0 To myR.Cells.Count - 1)
'Populate the array
For i = 0 To myR.Cells.Count - 1
myArray(i) = "[MASTER].[Reporting Period].[Reporting Period].[" & myR.Cells(i + 1).Value & "]"
Next i
Field.VisibleItemsList = myArray ' Error now says data not found in OLAP cube previous errors were unable to set property.
Exit For
'End With
End If
Next pvtfld
End With
End SubAny assistance would be greatly appreciated . I must update a number of sheets and pivottables for reports next week and do not want to have to filter each manually
Last edited by a moderator:
