Home VBA to update pivot field

# VBA to update pivot field

snoh8r
1#
snoh8r Published in 2017-12-07 20:34:08Z
 I recorded this: With ActiveSheet.PivotTables("PivotTable1").PivotFields("Min Closed Mth") .PivotItems("2016-10").Visible = False .PivotItems("2017-11").Visible = True End With  Each month, I run this report as a running 13. This was ran this month. We subtract two months back and then add one month back. I hope that makes sense. Anyway, while this code works for December, it isn't any good going to each month forward. I also need to address that each January the year is different. I've come up with this: Sub Update_Pivots() Dim thisMonth As Integer Dim lastMonth As Integer Dim thisYear As Integer If Month(Date) = 1 Then thisYear = Year(Date) - 1 thisMonth = Month(Date) - 1 lastMonth = Month(Date) - 2 Else thisYear = Year(Date) thisMonth = Month(Date) - 1 lastMonth = Month(Date) - 2 End If With ActiveSheet.PivotTables("PivotTable1").PivotFields("Min Closed Mth") .PivotItems("[thisYear].&[-]&.[lastMonth]").Visible = False .PivotItems("[thisYear].&[-]&.[thisMonth]").Visible = True End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("Min Closed Mth") .PivotItems("[thisYear].&[-]&.[lastMonth]").Visible = False .PivotItems("[thisYear].&[-]&.[thisMonth]").Visible = True End With End Sub  I get a run-time '1004 error Unable to get the PivotItems property of the PivotField class. Nevermind that while the dates work for January, they don't for February because thisMonth and Year should be current but lastMonth and year should have previous year. This is my first post. I've been doing a lot of simple stuff with VBA but now because of that, I'm being asked to do more. Appreciate the help.
YowE3K
2#
 Just use DateAdd to calculate the correct months, and then format that as "yyyy-mm": Sub Update_Pivots() With ActiveSheet.PivotTables("PivotTable1").PivotFields("Min Closed Mth") .PivotItems(Format(DateAdd("m", -14, Date),"yyyy-mm")).Visible = False .PivotItems(Format(DateAdd("m", -1, Date),"yyyy-mm")).Visible = True End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("Min Closed Mth") .PivotItems(Format(DateAdd("m", -14, Date),"yyyy-mm")).Visible = False .PivotItems(Format(DateAdd("m", -1, Date),"yyyy-mm")).Visible = True End With End Sub