ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Manipulating a Pivot table....HELP!!... (https://www.excelbanter.com/excel-programming/375414-manipulating-pivot-table-help.html)

WhytheQ
Manipulating a Pivot table....HELP!!...
 
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
Manipulating a Pivot table....HELP!!...
 
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 wrote:
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.

WhytheQ
Manipulating a Pivot table....HELP!!...
 
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 -

RadarEye
Manipulating a Pivot table....HELP!!...
 
Hi WhytheQ

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

Rgds,
RadarEye


WhytheQ wrote:
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 -

WhytheQ
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 -


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com