Pivot Table Problem

S

Sajith

hi,

I want to un select some of the items in the child items
of a dimension in pivot Table. So for this I have written
a query. But unselecting part, visible property is not
working.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always the value is
true. When i checked microsoft site, its written visible
type is read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

Thanks in advance for any help regarding this.

Sajith (code is below...)



Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False


'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable
 
D

Dave Peterson

You have a reply (general suggestions--no solution) at your other post.
hi,

I want to un select some of the items in the child items
of a dimension in pivot Table. So for this I have written
a query. But unselecting part, visible property is not
working.

Tried to update "pi.Visible" = False when even year =
2003 . But it is not updating. Seems like visible
property of pivot items is read only. Always the value is
true. When i checked microsoft site, its written visible
type is read/write boolean. where I am going wrong ?

Or Visible property of Pivot items will not work for OLAP
data source (my data source is poiting to analysis
Manager) ??

Thanks in advance for any help regarding this.

Sajith (code is below...)

Dim pf As PivotField
Dim pfmonth As PivotField
Dim pi As PivotItem
Dim Pimonth As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set pf = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]")

Set pfmonth = ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Month]")
Worksheets("Sheet2").PivotTables("PivotTable1") _
.PivotFields("[Week Date].[Week Date]").PivotItems
("22/09/2003").Visible = False

'For Each Pimonth In pfmonth.ChildItems
' If Pimonth.ChildItems = "01/01/2004" Then
' End If

'Next Pimonth
Application.ScreenUpdating = True
Application.DisplayAlerts = True

On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If IsDate(pi.Caption) Then
If Year(CDate(pi.Caption)) = 2004 Then
pi.Visible = True

Else
pi.Visible = False

End If
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Click to expand...
 

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

Similar Threads

VBA Pivot Table Error 1
Macro Help in Pivot 2
Help with code for selecting pivot table field by typing in a cell 2
Several Pivot Tables: Change one filter and adapt it for the other 2
Filter a pivot table with large number of unique items in the filterlist 1
pivot table show items with a value 1
Pivot table calculated field 3
Pivot Table Change to US Date Format - Why? 2

Top