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

[Keerthi@21]

Dear all,

I need an help using macro in pivot table filter. I have two pivot sheets, each should be refreshed and below auto population needs to be done using macro. Attached the excel data file for your reference.

sheet -> "Item-Pencil " - Pivot 1- refresh the pivot select the Month to previous Month i.e. if we are 12th month select 11th month and if we are in feb 21, select Jan - 21. Column "A2 " value to be populating correct month sheet -> "Item-Pen set" Pivot 2- refresh the pivot select the Month to previous Month i.e. if we are 12th month select 11th month and if we are in feb 21, select Jan - 21. Column "B2 " value to be populating correct month

Once respective columns updated, save the macro file and exist.

Thanks for your help in advance.

[p45cal]

In the attached, the pivot tables on sheets Item-Pencil and Item-Pen Set will filter themselves to the previous month to the system date month, when that sheet is activated.
You can alter any of the filters, but the OrderDate will always renew its filter on sheet activation.
The data in columns A:H of those two sheets are ignored and left alone.

[Keerthi@21]

Hi p45Cal,

Thanks for the code. But the actual help which I needed is something different. Please find the updated help needed:

Once the pivot refresh individually the below thing should be done

sheet -> "Item-Pencil " - Pivot 1- refresh the pivot select the Month to previous Month i.e. if we are 12th month select 11th month and if we are in feb 21, select Jan - 21.

Column "L2 " value to be populating correct month


Pivot 2- refresh the pivot select the period to last YTD periods i.e. if we are in 12th month select 1 to 11th month value and if we are in feb 21, select only jan 21 month

Column "L2 " value to be populating correct month.

The above mentioned month selection should be in number format like 1 to 11 instead of Jan -Dec.



Thanks for your help in advance.

[p45cal]

see attached

[Keerthi@21]

Hi p45cal,

Thanks for the updated code but still one thing is missed. Can you please check below and confirm?

Also Please confirm whether refresh of those pivots individually been included in your code. If not i need to update the code for the same.

sheet -> "Item-Pen Set

Pivot 2- refresh the pivot and if we are in 12th month select 1 to 11th month value and if we are in feb 21, select only jan 21 month

Column "L2 " value to be populating correct month.

[p45cal]

See attached.

Also Please confirm whether refresh of those pivots individually been included in your code. If not i need to update the code for the same.

You should be able to work this out for yourself.

[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.