Showing/Hiding Pivot-Items
 

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").Cur rentPage =
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?

Showing/Hiding Pivot-Items
 

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

Jim Thomlinson


"amac" wrote:

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").Cur rentPage =
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?

Showing/Hiding Pivot-Items
 

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



amac wrote:
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").Cur rentPage =
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?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html