Showing/Hiding Pivot-Items

A

amac

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?
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top