VBA Pivot Table

[sricc]

I have created a pivot table with vba that specifies fields such as:
With PT
.PivotFields("Billing").Orientation = xlRowField
.PivotFields("Billing").Position = 1
.PivotFields("Billing").Subtotals(1) = False
.PivotFields("Supplier").Orientation = xlRowField
.PivotFields("Supplier").Position = 2
.PivotFields("Supplier").Subtotals(1) = False
.PivotFields("Year").Orientation = xlColumnField
.PivotFields("Year").Position = 1
.PivotFields("Year").Subtotals(1) = False
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("Month").Position = 2
.PivotFields("Month").Subtotals(1) = False
.PivotFields("Total").Orientation = xlDataField
.PivotFields("Total").Subtotals(1) = False
.RowAxisLayout xlTabularRow
.RepeatAllLabels xlRepeatLabels
End With

For any PivotFields("Year").PivotItems less than the year prior to the current year, I want to hide.

I used the recorder and got:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.PivotItems("2017").Visible = False
.PivotItems("2018").Visible = False
.PivotItems("2019").Visible = False
.PivotItems("2020").Visible = False
.PivotItems("2021").Visible = False
End With
...but I don't know how to dynamically execute it with the creation of the PivotTable.

 

[Domenic]

Assuming that you've added a PivotTable and assigned it to your variable PT, try...

    Dim pt As PivotTable
    Dim pi As PivotItem
   
    With pt
   
        With .PivotFields("Billing")
            .Orientation = xlRowField
            .Position = 1
            .Subtotals(1) = False
        End With

        With .PivotFields("Supplier")
            .Orientation = xlRowField
            .Position = 2
            .Subtotals(1) = False
        End With

        With .PivotFields("Year")
            .Orientation = xlColumnField
            .Position = 1
            .Subtotals(1) = False
        End With

        With .PivotFields("Month")
            .Orientation = xlColumnField
            .Position = 2
            .Subtotals(1) = False
        End With
       
        .PivotFields("Total").Orientation = xlDataField
       
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
       
        For Each pi In .PivotFields("Year").PivotItems
            If pi.Value < Year(Date) - 1 Then
                pi.Visible = False
            End If
        Next pi
       
    End With

Hope this helps!

 

[sricc]

Assuming that you've added a PivotTable and assigned it to your variable PT, try...

    Dim pt As PivotTable
    Dim pi As PivotItem
  
    With pt
  
        With .PivotFields("Billing")
            .Orientation = xlRowField
            .Position = 1
            .Subtotals(1) = False
        End With

        With .PivotFields("Supplier")
            .Orientation = xlRowField
            .Position = 2
            .Subtotals(1) = False
        End With

        With .PivotFields("Year")
            .Orientation = xlColumnField
            .Position = 1
            .Subtotals(1) = False
        End With

        With .PivotFields("Month")
            .Orientation = xlColumnField
            .Position = 2
            .Subtotals(1) = False
        End With
      
        .PivotFields("Total").Orientation = xlDataField
      
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
      
        For Each pi In .PivotFields("Year").PivotItems
            If pi.Value < Year(Date) - 1 Then
                pi.Visible = False
            End If
        Next pi
      
    End With

Hope this helps!

With a little formatting, this works great! Thank you so much!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!