none
Filtering OLAP Pivot Table Based on a Cell RRS feed

  • Question

  • Hello, I have an OLAP pivot table that I wish to change the filter based on a cell value (that changes based on CUBERANKEDMEMBER of a Slicer).  However, I get "Run-time error 1004 - Unable to get the PivotFields property of the PivotTable class." The error points to: Set Field = pt.PivotFields("Alias"). Here is what I have so far:

    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
    'H6 is touched
    If Intersect(Target, Worksheets(1).Range("H6")) Is Nothing Then Exit Sub
    
    'Set Variables
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
    
    Set pt = Worksheets(1).PivotTables("PivotTableDCM")
    Set Field = pt.PivotFields("Alias")
    NewCat = Worksheets(1).Range("H6").Value
    
    'This updates and refreshes the PIVOT table
    With pt
    Field.ClearAllFilters
    Field.CurrentPage = NewCat
    pt.RefreshTable
    End With
    
    
    End Sub
    
    I am basically swinging through this blind, as I have no real VBA training other than what I have been teaching myself as I work through projects like this, so any further explanation would be appreciated. Thanks

    Wednesday, July 19, 2017 1:15 PM

Answers

  • Hi FluffyPanda13,

    The error message means that there is no such field called Alias , please check spelling or if there are unexpected space at the start or end of the field name.

    Here is the example.

    Sub SetPivotField()

    Dim pt As PivotTable

    Dim Field As PivotField

    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables(1)

    For Each pf In pt.PivotFields

    If Trim(pf.Name) = "Alias" Then

    Set Field = pf

    Exit For

    End If

    Next pf

    If Field Is Nothing Then

    MsgBox "There is no field called Alias"

    End If

    End Sub

    Best Regards,

    Terry

    • Marked as answer by FluffyPanda13 Friday, July 21, 2017 2:17 PM
    Friday, July 21, 2017 2:18 AM

All replies

  • Hi FluffyPanda13,

    The error message means that there is no such field called Alias , please check spelling or if there are unexpected space at the start or end of the field name.

    Here is the example.

    Sub SetPivotField()

    Dim pt As PivotTable

    Dim Field As PivotField

    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables(1)

    For Each pf In pt.PivotFields

    If Trim(pf.Name) = "Alias" Then

    Set Field = pf

    Exit For

    End If

    Next pf

    If Field Is Nothing Then

    MsgBox "There is no field called Alias"

    End If

    End Sub

    Best Regards,

    Terry

    • Marked as answer by FluffyPanda13 Friday, July 21, 2017 2:17 PM
    Friday, July 21, 2017 2:18 AM
  • Checked and double checked for possible typos or inconsistencies but didn't find any. Tried recording a simple macro and it said that the "pivotfield" was a "cubefield" (?). So there might be some disparity between what is being shown in the pivot table compared to what is being pulled by the Cube. Either way, decided to go a different route.

    Thank you for your reply though.

    Friday, July 21, 2017 2:19 PM