| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing/Manipulating Legend range for a Pivot Chart in XL2010 vsXL2003 and below. | Charts and Charting in Excel | |||
| Manipulating pivot table fields ... | Excel Discussion (Misc queries) | |||
| Manipulating Pivot Table Data Fields | Charts and Charting in Excel | |||
| Manipulating Pivot Tables With Macros | Excel Programming | |||
| Manipulating Pivot Tables with VBA | Excel Programming | |||
Linear Mode
