change CubeFields values using VBA / PowerPivot RRS feed

  • Question

  • Hi All

    I am trying to fix my code (below) because it give me an error in the bolded line:

    Sub LoopReport()
    ' changing/looping Cube.Fields based on the list from Worksheets("Spread")

    Dim i As Long
    Dim LastRow As Long
    Dim Spread2 As Variant
    Dim Pivot As PivotTable

    LastRow = Worksheets("Spread").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To LastRow
        Spread2 = Worksheets("Spread").Cells(i, 1).Value
        For Each Pivot In Worksheets("Report").PivotTables.PivotFields
    Pivot.CubeFields("Spread No & Market").Value = Spread2
        Next Pivot
    Next i

    End Sub

    What I am trying to do is described on the picture:

    I recorded a macro when manually changed value for Spread No & Market to 124 POLAND

    and it gives me this:

        ActiveSheet.PivotTables("PivotTable1").PivotFields( _
            "[Biblia - data].[Spread No & Market & Market].[Spread No & Market]"). _
            VisibleItemsList = Array( _
            "[Biblia - data].[Spread No & Market].&[124 POLAND]")


    [Biblia - data] - is my data source table in PowerPivot model

    [Spread No & Market] - is a column in [Biblia - data]

    Maybe the second code will be easier to modify.

    For instance.  

    ->Instead of [124 POLAND] should be a loop (POLAND 100, ROMANIA 100, POLAND 102....)

    -> Instead of ActiveSheet.PivotTables("PivotTable1") should be all pivots in Worksheets("Report")

    How it should look like?

    I am using excel 2013

    Wednesday, September 30, 2015 9:05 AM

All replies

  • Currently my code looks like below (after some modifications). It works without errors but do not filter my pivot tables.

    Really no one can help?

    Sub CreatePresentation()

    Dim i As Long
    Dim LastRow As Long
    Dim Spread2 As Variant
    Dim pvtTable As PivotTable
    Dim pvtTable2 As PivotTable
    Dim pvtField As PivotField
    Dim pvtField2 As PivotField
    Dim pvtItem As PivotItem

    Application.ScreenUpdating = False

    Set pvtTable = Worksheets("Report").PivotTables("PivotTable1")
    Set pvtTable2 = Worksheets("Report").PivotTables("PivotTable3")
    Set pvtField = pvtTable.PivotFields("[Biblia - data].[Spread Mapping & Market].[Spread Mapping & Market]")
    Set pvtField2 = pvtTable2.PivotFields("[Biblia - data].[Spread Mapping & Market].[Spread Mapping & Market]")
    LastRow = Worksheets("Spread").Cells(Rows.Count, 3).End(xlUp).Row

    Call OpenPowerPoint

    For i = 2 To LastRow
     Call DeletePics
     Spread2 = Worksheets("Spread").Cells(i, 3).Value

        For Each pvtItem In pvtField.PivotItems
            If pvtItem.Caption = Spread2 Then
                pvtItem.Visible = True
                pvtItem.Visible = False
            End If
        Next pvtItem
            For Each pvtItem In pvtField2.PivotItems
            If pvtItem.Caption = Spread2 Then
                pvtItem.Visible = True
            End If
        Next pvtItem
     Call InsertPics
     Call AddSlidesToPPT
    Next i
    Application.ScreenUpdating = True
    End Sub

    Thursday, October 1, 2015 3:51 PM