none
vba to modify PivotFields in mulitple PivotTables RRS feed

  • Question

  • Hi All

    I wrote the code below but it gives me an error in a bolded line:

    Sub LoopReport()

    Dim i As Integer
    Dim LastRow As Integer
    Dim Spread As Range
    Dim Pivot As PivotTable
    Dim PivotField As PivotField

    LastRow = Worksheets("Spread").Cells(Rows.Count, 2).End(xlUp).Row

    For i = 2 To LastRow
    Call DeletePics
    Set Spread = Worksheets("Spread").Cells(i, 2).Value
        For Each Pivot In Worksheets("Report").PivotTables
            With PivotFields("Spread No#")
                .Value = Spread
            End With
        Next Pivot
    Call InsertPics

    Call CopyToPPT

    Next i

    End Sub

    Generally what I want to do is to modify the PivotField "Spread No#" in all PivotTables in the "Report" sheet. 

    This modification rely on changing "Spread No#" based on the values from sheet "Spread"

    All PivotTables  are PowerPivot Tables created in excel 2013

    How the proper code should look like and what is wrong with this one?


    Monday, September 28, 2015 12:20 PM

All replies

  • Change the offending line to

            With Pivot.PivotFields("Spread No#")

    I'd do it like this:

    Sub LoopReport()
        Dim i As Long
        Dim LastRow As Long
        Dim Spread As Variant
        Dim Pivot As PivotTable
    
        LastRow = Worksheets("Spread").Cells(Rows.Count, 2).End(xlUp).Row
        For i = 2 To LastRow
            Call DeletePics
            Spread = Worksheets("Spread").Cells(i, 2).Value
            For Each Pivot In Worksheets("Report").PivotTables
                Pivot.PivotFields("Spread No#").Value = Spread
            Next Pivot
            Call InsertPics
            Call CopyToPPT
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 28, 2015 12:35 PM
  • Thanks Hans

    But now I got the another error in the bolded line:

    Sub LoopReport()
        Dim i As Long
        Dim LastRow As Long
        Dim Spread As Variant
        Dim Pivot As PivotTable
    
        LastRow = Worksheets("Spread").Cells(Rows.Count, 2).End(xlUp).Row
        For i = 2 To LastRow
            Call DeletePics
            Spread = Worksheets("Spread").Cells(i, 2).Value
            For Each Pivot In Worksheets("Report").PivotTables
                Pivot.PivotFields("Spread No#").Value = Spread
            Next Pivot
            Call InsertPics
            Call CopyToPPT
        Next i
    End Sub

    My Power PivotTables Fields looks like this:

       If I record a macro and add Spread No# to my PowerPivot Table I will get the code:

        With ActiveSheet.PivotTables("PivotTable3").CubeFields( _
       "[Biblia - data].[Spread No#]")

    Maybe CubeFields should be instead of Spread No#  ?

    Monday, September 28, 2015 3:39 PM
  • It's worth a try. Or try PageFields instead of PivotFields.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 28, 2015 4:23 PM
  • Still an issue

    1) If I add PageFields I will get this error in the bolded line:


    Sub LoopReport()

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

    LastRow = Worksheets("Spread").Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To LastRow
        Spread = Worksheets("Spread").Cells(i, 2).Value
        For Each Pivot In Worksheets("Report").PivotTables
            Pivot.PageFields("Spread No#").Value = Spread
        Next Pivot
    Next i

    End Sub

    2) If I add CubeFields I will get the error in the bolded part:


    Sub LoopReport()

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

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

    End Sub

    What can be wrong?


    Tuesday, September 29, 2015 7:58 AM