Macro filtering a pivot by month

[Cele]

Can you please help me with the macro called Confirm_This_Month()

I want to be able to have it confirm the entries for the current month we are in, automatically, without having me to have manually fix the macro every month, please help.

Thank you



Cele

 

[NARAYANK991]

Hi ,

Please attach your workbook , with data and code in it.

Narayan

 

[Cele]

i did, please see above. thanks.

 

[Chihiro]

I'm not sure if I completely understand what you are trying to accomplish here...

But try this code.

Sub Confirm_This_Month()
Dim pItem As PivotItem
    With Sheets("Pivotcurrentyr").PivotTables("confirmedthismon").PivotFields( _
        "Month (confirmed)")
        On Error Resume Next
        For Each pItem In .PivotItems
            If Not pItem.Visible Then pItem.Visible = True
        Next
        For Each pItem In .PivotItems
            If pItem.Value = "" Then pItem.Visible = False
            If CDate(pItem.Value) <> DateSerial(Year(Date), Month(Date), 1) Then
                pItem.Visible = False
            Else
                pItem.Visible = True
            End If
        Next
    End With
    ThisWorkbook.Sheets("Pivotprioryr").Visible = False
    ThisWorkbook.Sheets("Pivotcurrentyr").Visible = False
    ThisWorkbook.Sheets("refreshallpivots").Visible = False

    ActiveWorkbook.Worksheets(2).Activate
End Sub

Edit: If you want just the current month. Then change ">" operator to "<>".

If CDate(pItem.Value) <> DateSerial(Year(Date), Month(Date), 1) Then

Edit2: Code updated to include additional loop to avoid no item visible error.

 

[Cele]

This exactly what I needed. Many thanks!!!

Celeste