CODEWITHSUNDEEP
excelvbapivot-table
Rafael Rodrigues Santos
Rafael Rodrigues Santos

Reputation: 502

VBA PivotTable error when updating table in a loop

I'm having one issue to perform a loop for 1 pivot table on excel file: I just want to change the model2 from IJto CV

For i = 7 To 11

    ActiveSheet.PivotTables("Tabela  
    dinâmica1").PivotFields("MODEL2").CurrentPage _
         = "(All)"
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("MODEL2")
        .PivotItems("CV").Visible = False
        .PivotItems("IJ").Visible = True
        .PivotItems("(blank)").Visible = False
    End With
    cel4 = "AV" & i
    cel3 = "AX" & i
    Worksheets("DDTZ C").Range("AZ" & i).Formula = "=Iferror(IF(" & cel4 &   
    "="""","""",VLookup(" & cel3 & ", AT:AV, 3, False)),"""")"

    ActiveSheet.PivotTables("Tabela 
    dinâmica1").PivotFields("MODEL2").CurrentPage _
         = "(All)"
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("MODEL2")
         .PivotItems("CV").Visible = True
         .PivotItems("IJ").Visible = False
         .PivotItems("(blank)").Visible = False
    End With

    Worksheets("DDTZ C").Range("BA" & i).Value = "=Iferror(IF(" & cel4 & 
    "="""","""",VLookup(" & cel3 & ", AT:AV, 3, False)),"""")"

Next i

This loop has worked perfectly on first loop, however, when it start the second loop i = 8, an error appears to me, I have checked the script, and the problem is related to the followed part:

    ActiveSheet.PivotTables("Tabela  
         dinâmica1").PivotFields("MODEL2").CurrentPage _
         = "(All)"
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("MODEL2")
        .PivotItems("CV").Visible = False
        .PivotItems("IJ").Visible = True
        .PivotItems("(blank)").Visible = False
    End With

I cannot understand why I'm having a problem with that, since it works fine on the first loop i = 7

Anyone can help with that issue? Thanks in advance

Upvotes: 0

Views: 67

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below.

Note: I've debugged only the PivotTable section, not the formula.

Dim PvtTbl As PivotTable

' set the Pivot Table into a variable
Set PvtTbl = ActiveSheet.PivotTables("Tabela dinâmica1")
Dim i As Long

For i = 7 To 11
    With PvtTbl
        With .PivotFields("MODEL2")
            .ClearAllFilters
            .PivotItems("CV").Visible = False
            .PivotItems("(blank)").Visible = False
        End With

        cel4 = "AV" & i
        cel3 = "AX" & i

        ' === Not debugging your formula part ===
        Worksheets("DDTZ C").Range("AZ" & i).Formula = "=Iferror(IF(" & cel4 &
        "="""","""",VLookup(" & cel3 & ", AT:AV, 3, False)),"""")"

        With .PivotFields("MODEL2")
            .ClearAllFilters
            .PivotItems("IJ").Visible = False
            .PivotItems("(blank)").Visible = False
        End With

        ' === Not debugging your formula part ===
        Worksheets("DDTZ C").Range("BA" & i).Value = "=Iferror(IF(" & cel4 &
        "="""","""",VLookup(" & cel3 & ", AT:AV, 3, False)),"""")"
    End With
Next i

Upvotes: 2

Related Questions