locked
How to control Excel pivot tables from a cell value with VBA (OLAP) RRS feed

  • Question

  • Hi!

    I am trying to steup a maco that will change the filter in a pivot table if a certain cell is changed. The source is an OLAP. In my case, I want cell B3 to decide the filter. I have been searching online for help, and found this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    'This line stops the worksheet updating on every change, it only updates when cell
    'H6 or H7 is touched
    If Intersect(Target, Range("B3:B4")) Is Nothing Then Exit Sub

    'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String

    'Here you amend to suit your data (Data is the nam eof my sheet also, and R12 is the pivot). The source is a OLAP, and my filters have multiple levels. In this case the filter is called Custmer Group, and contains customer group and custumer)
    Set pt = Worksheets("Data").PivotTables("R12")
    Set Field = pt.PivotFields("[Customer].[Customer Group].[Customer]")
    NewCat = Worksheets("Data").Range("B3").Value

    'This updates and refreshes the PIVOT table. The field with underline and bold is where my macro stops. 
    With pt
    Field.ClearAllFilters
    Field.CurrentPageName = NewCat
    pt.RefreshTable
    End With

    End Sub

    Does anyone know how to set this up? Any help will be much appreciated! Thanks in advance.

    Wednesday, October 21, 2020 7:45 PM