PDA

View Full Version : Show selected months in Pivot table



expfresocspy
01-09-2012, 08:42 PM
So I want to show only the current month and the 5 months before . Meaning , If the current month is February , my pivot table should show values from September , October , November , December , January nd February .

Here is my code :

ColumnFields:=Array("Month Submitted"), _


For Each PivItem In _
PT.PivotFields("Month Submitted").PivotItems
PivItem.Visible = True
Next PivItem

For Each PivItem In _
PT.PivotFields("Month Submitted").PivotItems
Select Case PivItem.Name
Case Current Date to the last 5 months
PivItem.Visible = True
Case Else
PivItem.Visible = False
End Select
Next PivItem

Thank you so very much for the help ! :D

Zack Barresse
01-09-2012, 11:42 PM
Hello there,

Assuming you have your month names as strings, perhaps you could use the following...
Dim sMonth0 As String
Dim sMonth1 As String
Dim sMonth2 As String
Dim sMonth3 As String
Dim sMonth4 As String
Dim sMonth5 As String

sMonth0 = MonthName(DateSerial(Year(VBA.Date()), Month(VBA.Date()), 1), False)
sMonth1 = MonthName(DateSerial(Year(VBA.Date()), Month(VBA.Date()) - 1, 1), False)
sMonth2 = MonthName(DateSerial(Year(VBA.Date()), Month(VBA.Date()) - 2, 1), False)
sMonth3 = MonthName(DateSerial(Year(VBA.Date()), Month(VBA.Date()) - 3, 1), False)
sMonth4 = MonthName(DateSerial(Year(VBA.Date()), Month(VBA.Date()) - 4, 1), False)
sMonth5 = MonthName(DateSerial(Year(VBA.Date()), Month(VBA.Date()) - 5, 1), False)

For Each PivItem In PT.PivotFields("Month Submitted").PivotItems
PivItem.Visible = True
Next PivItem

For Each PivItem In _
PT.PivotFields("Month Submitted").PivotItems
Select Case PivItem.Name
Case sMonth0, sMonth1, sMonth2, sMonth3, sMonth4, sMonth5
PivItem.Visible = True
Case Else
PivItem.Visible = False
End Select
Next PivItem
HTH