Manipulating a Pivot table....HELP!!...

[WhytheQ]

Dear All,
I've got the following code which nearly works!

'==================================
Sub Change_Pivot_Months()

Dim Worksheet
Dim Pivot
Dim PivotItem
Dim myLatestMonth

For Each Worksheet In Worksheets
For Each Pivot In Worksheet.PivotTables
For Each PivotItem In Pivot.PivotFields("Month").PivotItems
Select Case PivotItem
Case Format(Range("Month1"), "mmm-yy")
PivotItem.Visible = True
Case Format(Range("Month2"), "mmm-yy")
PivotItem.Visible = True
Case Format(Range("Month3"), "mmm-yy")
PivotItem.Visible = True
Case Else
PivotItem.Visible = False
End Select
Next PivotItem
Next
Next Worksheet

End Sub
'====================================

The aim of the above was to have it so that each pivottable in the
workbook will only show the months in the 3 cells spoecified in the
worksheet (Range("Month1")...) and to make all other months
visible=False.

The above seems to fall down when it hits "PivotItem.Visible = True"

Can anyone help?...it would be well appreciated.
Regards,
Jason.

 

[RadarEye]

Hi WhytheQ,

You are close,

I changed it into then and is seems to work:

Dim myWorksheet As Worksheet
Dim myPivot As PivotTable
Dim myPivotItem As PivotItem

For Each myWorksheet In Worksheets
For Each myPivot In myWorksheet.PivotTables
For Each myPivotItem In
myPivot.PivotFields("Month").PivotItems
Select Case myPivotItem
Case Format(Range("Month1"), "mmm-yy")
myPivotItem.Visible = True
Case Format(Range("Month2"), "mmm-yy")
myPivotItem.Visible = True
Case Format(Range("Month3"), "mmm-yy")
myPivotItem.Visible = True
Case Else
myPivotItem.Visible = False
End Select
Next myPivotItem
Next
Next myWorksheet


HTH,

RadarEye.

 

[WhytheQ]

nice one RE ! is the only change to the variable declarations?

Rgds,
J

 

[RadarEye]

Hi WhytheQ

The variable name you used might be a reserved word.
this goes for WorkSheet!

Rgds,
RadarEye

 

[WhytheQ]

Thanks

J

HiWhytheQ

The variable name you used might be a reserved word.
this goes for WorkSheet!

Rgds,
RadarEye



WhytheQwrote: