Showing/Hiding Pivot-Items

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
Back
Top