Setting a rolling month on the date filter of a Pivot Table

[adaircameron]

Hi,

I have been trying to figure out a way of setting a rolling month/year on the date filter of a pivot table, can someone advise?

I read on one blog that a set needs to be created from with the 'Options' tab of the 'PivotTable Tools'. Unfortunately, this is always disabled so cannot select any of the following from the 'Fields, ITems, & Sets' dropdown:

• Create set based on row items
• Create set based on column items
• Manage sets

Is there any other ways of doing this?

 

[pbornemeier]

Link to some VBA code that can be adapted to set a pivot table field to the current month and year:

http://www.mrexcel.com/forum/showthread.php?t=539815

 

[adaircameron]

Link to some VBA code that can be adapted to set a pivot table field to the current month and year:

http://www.mrexcel.com/forum/showthread.php?t=539815

Thanks! I will try giving this a good go, gone over my head a little but I'll try figuring it out.

 

[pbornemeier]

This may be a little closer to what you need. It will still require some modification:

Sub MakeCurrentMontyAndYearVisibleInPivotTable()
'
    Dim iX As Integer
    Dim bFound As Boolean
 
    Range("A4").Select 'Select Date Header in Pivot Table
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, True) 'Group by Months & Years
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Years")
        bFound = False
        For iX = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems.Count
            If ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems(iX).Name = CStr(Year(Now())) Then
                bFound = True 'Current Year data is present
                Exit For
            End If
        Next
        If bFound = True Then
            'make current year pivotitem visible
            .PivotItems(CStr(Year(Now()))).Visible = True
            'Hide all the others
            For iX = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems.Count
                Select Case ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems(iX).Name
                Case CStr(Year(Now()))
                    'Already made visible = do nothing
                Case Else
                    .PivotItems(iX).Visible = False 'hide it
                End Select
            Next
        Else
            MsgBox "No data for " & Year(Now()) & " is present"
            GoTo End_Sub
        End If
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("date1") 'date1 was the name of my date field
                                                                     'I am not sure why this was not "Months"
        bFound = False
        For iX = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("date1").PivotItems.Count
            If ActiveSheet.PivotTables("PivotTable1").PivotFields("date1").PivotItems(iX).Name = Format(Now(), "mmm") Then
                bFound = True 'Current Year data is present
                Exit For
            End If
        Next
        If bFound = True Then
            'make current year pivotitem visible
            .PivotItems(Format(Now(), "mmm")).Visible = True
            'Hide all the others
            For iX = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("date1").PivotItems.Count
                Select Case ActiveSheet.PivotTables("PivotTable1").PivotFields("date1").PivotItems(iX).Name
                Case Format(Now(), "mmm")
                    'Already made visible = do nothing
                Case Else
                    .PivotItems(iX).Visible = False 'hide it
                End Select
            Next
        Else
            MsgBox "No data for " & Format(Now(), "mmm") & " is present"
            GoTo End_Sub
        End If
    End With
 
End_Sub:
End Sub