RE: Pivot filter Month selection - Automatic Population in respective column

[Keerthi@21]

Hi p45al,


Thank you so much for your code and patience. But, I could see the same logic in both pivot sheets.


can you please help me to fix it by clicking on button and run those things individually, instead of selecting filter while activating the sheet.


I have applied the same code in by using two buttons on each sheet in my original data, I am facing unable to get the pivotitems property of the pivotfield class error - Higlighted in Bold.


Code for your reference:

MTD Code:

MTD - Pivot 1.jpg


Sub Activate()






With ActiveSheet.PivotTables("PivotTable2").PivotFields("Period")
'.ClearAllFilters
  VisPIName = CStr(Month(DateAdd("m", -1, Date)))
  '.PivotItems(VisPIName).Visible = False
  .PivotItems(VisPIName).Visible = True
  Set yyy = .PivotItems
  For Each pvti In .PivotItems
    pvti.Visible = pvti.Name = VisPIName
  Next pvti
End With


Call UpdatePivotTableRange_1


End Sub

YTD Code:

YTD-Pivot 2.jpg

Sub Activate_Copy()


'Call UpdatePivotTableRange_1


With ActiveSheet.PivotTables("PivotTable2").PivotFields("Period").ClearAllFilters
LastMonth = Month(DateAdd("m", -1, Date))
  VisPIName = CStr(LastMonth)
  .PivotItems(VisPIName).Visible = True
  Set yyy = .PivotItems
  On Error Resume Next
  For Each pvti In .PivotItems
    pvti.Visible = (pvti.Name = VisPIName) Or CLng(pvti.Name) <= LastMonth
  Next pvti
  On Error GoTo 0
End With
End Sub

Please help out. Thanks for your help in advance.