VBA Pivot Items

[csamy987]

Hello,

I'm trying to automatically set the current year and next year in a pivot table using VBA. This is what I have currently

Dim CurrentYear as Variant
Dim NextYear as Variant

CurrentYear = Year(Now())
NextYear = Year (Now())+1

With ActiveSheet.PivotTables("PivotTable1").PivotFields("YR")
.PivotItems("CurrentYear").Visible = True
.PivotItems("NextYear").Visible = True
End With

Every time I run it, I get an error. Can someone please help?

Thanks,

 

[JustynaMK]

Hi,

You're almost there. As you already noticed "PivotItems" only accepts strings, so the following line of code will work properly:

.PivotItems("2019").Visible = True

Now we need to force both CurrentYear and NextYear to convert years into strings and then use them in your code.

    Dim CurrentYear As Variant
    Dim NextYear As Variant
    
    CurrentYear = Format(Year(Now()), "0000")
    NextYear = Format(Year(Now()) + 1, "0000")
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("YR")
        .PivotItems(CurrentYear).Visible = True
        .PivotItems(NextYear).Visible = True
    End With

Hope it works for you.

 

[csamy987]

Worked perfectly. Thank you!!!

 

[JustynaMK]

Glad it worked for you! Take care.