Showing/Hiding Pivot-Items

[Guest]

I have the following script:
For i = 1 To ActiveWorkbook.Sheets().Count
Sheets(i).Select
For j = 1 To ActiveSheet.PivotTables().Count
ActiveSheet.PivotTables(j).PivotSelect "", xlOrigin
ActiveSheet.PivotTables(j).RefreshTable

ActiveSheet.PivotTables(j).PivotFields("Year").CurrentPage =
CurrYear
With ActiveSheet.PivotTables(j).PivotFields("YearMonth")
For k = 1 To .PivotItems.Count
If Val(.PivotItems(k)) <= Currmonth Then
If .PivotItems(k).Visible = False Then
.PivotItems(k).Visible = True
End If
Else
If .PivotItems(k).Visible = True Then
.PivotItems(k).Visible = False
End If
End If
Next 'k
End With
Range("A1").Select
Next 'j
Next 'i


I sometimes receive the error
RuntimeError: 1004
Unable to set the visibility Property of the PivotItems Class.

What can be the cause, how can it be solved?

 

[Guest]

You can not hide all of the items... That could be the problem...

 

[Debra Dalgleish]

If the field is set to Automatic sort, you may have problems. If that's
the case, to prevent the error, set the Sort for the field to Manual.
For example:

Dim intASO As Integer
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Employee")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("1").Visible = False
.PivotItems("42").Visible = True
.AutoSort intASO, .SourceName
End With