Manipulating a Pivot table....HELP!!...
Thanks
J
On Oct 26, 5:27 pm, "RadarEye" wrote:
HiWhytheQ
The variable name you used might be a reserved word.
this goes for WorkSheet!
Rgds,
RadarEye
WhytheQwrote:
nice one RE ! is the only change to the variable declarations?
Rgds,
J
On Oct 18, 7:39 pm, "RadarEye" wrote:
HiWhytheQ,
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.
WhytheQwrote:
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.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -
|