Set PivotItem using counter
 

Scenario: User inputs a month in a cell with range name "period". I need the
Pivot Table to show all months less than or equal to "period". Is there a
way to use a counter to set the visible property of PivotItem? My code:

Dim maxRange As Integer
Dim count As Integer
Dim strPeriod As String

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Pivot").PivotTables("pvtMain")
Set pf = pt.PivotFields("PERIOD_NUMBER")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

maxRange = Range("period").Value + 1

For count = maxRange To 12
strPeriod = count
If pi.Name = strPeriod Then
pi.Visible = False
End If
Next count

Set PivotItem using counter
 

To answer my own questions - if anybody else cares :-)

Dim maxRange As Integer
Dim count As Integer
Dim i As Integer

Dim Periods As Variant
Periods = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11")

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Pivot").PivotTables("pvtMain")
Set pf = pt.PivotFields("PERIOD_NUMBER")

Application.Calculation = xlManual

pt.RefreshTable

pf.AutoSort xlManual, "PERIOD_NUMBER"

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

maxRange = Range("period").Value + 1

For count = maxRange To 11
i = count - 1

With pf
.PivotItems(CStr(Periods(i))).Visible = False
End With

Next count

"CinqueTerra" wrote:

Scenario: User inputs a month in a cell with range name "period". I need the
Pivot Table to show all months less than or equal to "period". Is there a
way to use a counter to set the visible property of PivotItem? My code:

Dim maxRange As Integer
Dim count As Integer
Dim strPeriod As String

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Sheets("Pivot").PivotTables("pvtMain")
Set pf = pt.PivotFields("PERIOD_NUMBER")

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

maxRange = Range("period").Value + 1

For count = maxRange To 12
strPeriod = count
If pi.Name = strPeriod Then
pi.Visible = False
End If
Next count