Another how to change pivot filter in a OLAP cube based on a cell value

[Hans10]

Hi!

yes I know it - there are a lot of questions about this tast on the internet and also here om Mr Excel.

but still, i cant get right! - Please help me

I have several pivot tables based on a OLAP Cube (the same cube).
What i want is to update to a specific Date (from a Cell) in Excel 2010

My pivotfield name is (in danish):"[dato].[aarMaanedDagH].[AarmaanedDag].&[210150721]"

My Guess

step 1:
ActiveSheet.pivottables("test")

step 2:
"delete current filter" HOW?

step 3:
ActiveSheet.pivottables("test").pivotfields("[dato].[aarMaanedDagH].[AarmaanedDag].&[Range("A1")]")

Step 4:
ActiveSheet.pivottables("test").PivotCache.Refresh

Can somebody help me?

 

[jorismoerings]

hi, welcome to MrExcel,


take a look at the following. Try this code

P[COLOR=#333333]rivate Sub Worksheet_Change(ByVal Target As Range)[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit;">    
Dim ws As Worksheet
    Dim pt As PivotTable
    Const strField1 As String = "enter your own field name here!!"
    
    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$2" Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField1)
                .ClearAllFilters
                .CurrentPage = Target.Value
                End With
            Next pt
        Next ws
    End If
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True 
</code>[COLOR=#333333]End Sub[/COLOR]

change the target cell to whatever cell you're using as a reference and make sure you place this code on vba sheet of the sheet which actually contains the filter cell.

rgs,

 

[Hans10]

Thanks jorismoerings!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Const strField1 As String = "[Dato].[AarMaanedDagH].[AarMaanedDag]"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = "$G2$" Then
For Each ws In this.Workbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField1)
.ClearAllFilters
.CurrentPage = Target.Value
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Hmm Nothing happens...
What is wrong?

 

[Hans10]

Thanks jorismoerings!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Const strField1 As String = "[Dato].[AarMaanedDagH].[AarMaanedDag]"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = "$G2$" Then
For Each ws In this.Workbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField1)
.ClearAllFilters
.CurrentPage = Target.Value
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Hmm Nothing happens...
What is wrong?

Ohhh I need to put this code in the ws where the target "cell" is.

 

[Hans10]

Thx jorismoerings for your reply, but it doesn't work?!

I have this code and it is working, but i still want to use a celle instead of typing a new date value all the time!
my date format is "yyyymmdd" fx. 20150727

- some of the pivot tables dont have a date filter, is this case i only want to update the tables.

Sub Makro3()
'Opdaterer følgende Pivot tabeller fra følgende ark
Sheets("Overblik områder dags dato").Select
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
ActiveSheet.PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems _
= True
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array( _
"[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150727]")
ActiveSheet.PivotTables("Pivottabel1").PivotCache.Refresh

Sheets("Overblik områder dags dato").Select
ActiveSheet.PivotTables("Pivottabel2").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
ActiveSheet.PivotTables("Pivottabel2").CubeFields(47).EnableMultiplePageItems _
= True
ActiveSheet.PivotTables("Pivottabel2").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel2").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel2").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array( _
"[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150727]")
ActiveSheet.PivotTables("Pivottabel2").PivotCache.Refresh

Sheets("Overblik områder og dato").Select
ActiveSheet.PivotTables("Pivottabel1").PivotCache.Refresh

Sheets("Overblik områder og dato").Select
ActiveSheet.PivotTables("Pivottabel3").PivotCache.Refresh

Sheets("Opstilling CPR og dato maj juni").Select
ActiveSheet.PivotTables("Pivottabel1").PivotCache.Refresh

Sheets("Opstilling CPR dags dato").Select
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
ActiveSheet.PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems _
= True
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array( _
"[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150727]")
ActiveSheet.PivotTables("Pivottabel1").PivotCache.Refresh

Sheets("Borgere uden forløb").Select
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[Aar Maaned Dag].[Aar Maaned Dag]").ClearAllFilters
ActiveSheet.PivotTables("Pivottabel1").CubeFields(231).EnableMultiplePageItems _
= True
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[Aar Maaned Dag].[Aar Maaned Dag]").VisibleItemsList = Array( _
"[Dato].[Aar Maaned Dag].&[20150727]")
ActiveSheet.PivotTables("Pivottabel1").PivotCache.Refresh

Sheets("Forløb og GH og ydelser").Select
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
ActiveSheet.PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems _
= True
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel1").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array( _
"[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150727]")
ActiveSheet.PivotTables("Pivottabel1").PivotCache.Refresh


ActiveSheet.PivotTables("Pivottabel2").PivotCache.Refresh


ActiveSheet.PivotTables("Pivottabel3").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
ActiveSheet.PivotTables("Pivottabel3").CubeFields(47).EnableMultiplePageItems _
= True
ActiveSheet.PivotTables("Pivottabel3").PivotFields( _
"[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel3").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Pivottabel3").PivotFields( _
"[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array( _
"[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150727]")
ActiveSheet.PivotTables("Pivottabel3").PivotCache.Refresh