| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change PivotField Item with macro
Hi All
I have written a simple macro for a workbook that contains a great number of Pivot Tables (all linked to external databases or other pivottables). It adds a new item (number 2) to the field period; See PivotChangePeriod. Unfortunately, it does not work on all Pivot Tables and then gives the error message "Unable to set the visible property of the PivotItem class". I have no idea why it does this. All Pivot Tables that are covered by the macro contain field period with item 2 and I can manually add it without any problem (when added, it does not overwrite other pivot tables or something like that). Also, when I record this with the macro recorder, it gives the same codes as in the macro. Any idea what I might be doing wrong? regards, Hans Sub PivotChangePeriod() Dim PT As PivotTable Dim Sh1 As Sheets Dim wk As Worksheet Set Sh1 = Worksheets(Array('10sheets') For Each wk In Sh1 For Each PT In wk.PivotTables PT.PivotFields("period").PivotItems("2").Visible = True Next PT Next wk End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change PivotField Item with macro
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change PivotField Item with macro
William
Does not make a difference :( Hans -----Original Message----- Hans Untested, but try refreshing all pivot tables at the start of your macro. -- XL2002 Regards William "Hans" wrote in message ... | Hi All | | I have written a simple macro for a workbook that contains | a great number of Pivot Tables (all linked to external | databases or other pivottables). It adds a new item | (number 2) to the field period; See PivotChangePeriod. | | Unfortunately, it does not work on all Pivot Tables and | then gives the error message "Unable to set the visible | property of the PivotItem class". I have no idea why it | does this. All Pivot Tables that are covered by the macro | contain field period with item 2 and I can manually add it | without any problem (when added, it does not overwrite | other pivot tables or something like that). Also, when I | record this with the macro recorder, it gives the same | codes as in the macro. | | Any idea what I might be doing wrong? | | regards, | Hans | | Sub PivotChangePeriod() | | Dim PT As PivotTable | Dim Sh1 As Sheets | Dim wk As Worksheet | | Set Sh1 = Worksheets(Array('10sheets') | | For Each wk In Sh1 | For Each PT In wk.PivotTables | PT.PivotFields("period").PivotItems("2").Visible | = True | Next PT | Next wk | | End Sub | | . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change PivotField Item with macro
To prevent the error, set the Sort for the field to Manual. You can do
this in the code, for example: '================================== Sub PivotShowItemResetSort() 'For version 2000 -- show all items in field 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of PivotItem class 'returns sort order to previous setting Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim intASO As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.VisibleFields intASO = pf.AutoSortOrder pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems If pi.Visible < True Then pi.Visible = True End If Next pi pf.AutoSort intASO, pf.SourceName Next pf Next pt Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub '================================ Hans wrote: Hi All I have written a simple macro for a workbook that contains a great number of Pivot Tables (all linked to external databases or other pivottables). It adds a new item (number 2) to the field period; See PivotChangePeriod. Unfortunately, it does not work on all Pivot Tables and then gives the error message "Unable to set the visible property of the PivotItem class". I have no idea why it does this. All Pivot Tables that are covered by the macro contain field period with item 2 and I can manually add it without any problem (when added, it does not overwrite other pivot tables or something like that). Also, when I record this with the macro recorder, it gives the same codes as in the macro. Any idea what I might be doing wrong? regards, Hans Sub PivotChangePeriod() Dim PT As PivotTable Dim Sh1 As Sheets Dim wk As Worksheet Set Sh1 = Worksheets(Array('10sheets') For Each wk In Sh1 For Each PT In wk.PivotTables PT.PivotFields("period").PivotItems("2").Visible = True Next PT Next wk End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change PivotField Item with macro
William
I finally found out why it doesnt run on some pivottables...the autosort option of the field 'period' should be on manual and not 'ascending' or 'descending'. It works fine now. Hans -----Original Message----- Hans Untested, but try refreshing all pivot tables at the start of your macro. -- XL2002 Regards William "Hans" wrote in message ... | Hi All | | I have written a simple macro for a workbook that contains | a great number of Pivot Tables (all linked to external | databases or other pivottables). It adds a new item | (number 2) to the field period; See PivotChangePeriod. | | Unfortunately, it does not work on all Pivot Tables and | then gives the error message "Unable to set the visible | property of the PivotItem class". I have no idea why it | does this. All Pivot Tables that are covered by the macro | contain field period with item 2 and I can manually add it | without any problem (when added, it does not overwrite | other pivot tables or something like that). Also, when I | record this with the macro recorder, it gives the same | codes as in the macro. | | Any idea what I might be doing wrong? | | regards, | Hans | | Sub PivotChangePeriod() | | Dim PT As PivotTable | Dim Sh1 As Sheets | Dim wk As Worksheet | | Set Sh1 = Worksheets(Array('10sheets') | | For Each wk In Sh1 | For Each PT In wk.PivotTables | PT.PivotFields("period").PivotItems("2").Visible | = True | Next PT | Next wk | | End Sub | | . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change PivotField Item with macro
Hello Debra
I just found it out myself, but thanks for the macro! regards, Hans -----Original Message----- To prevent the error, set the Sort for the field to Manual. You can do this in the code, for example: '================================== Sub PivotShowItemResetSort() 'For version 2000 -- show all items in field 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of PivotItem class 'returns sort order to previous setting Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim intASO As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.VisibleFields intASO = pf.AutoSortOrder pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems If pi.Visible < True Then pi.Visible = True End If Next pi pf.AutoSort intASO, pf.SourceName Next pf Next pt Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub '================================ Hans wrote: Hi All I have written a simple macro for a workbook that contains a great number of Pivot Tables (all linked to external databases or other pivottables). It adds a new item (number 2) to the field period; See PivotChangePeriod. Unfortunately, it does not work on all Pivot Tables and then gives the error message "Unable to set the visible property of the PivotItem class". I have no idea why it does this. All Pivot Tables that are covered by the macro contain field period with item 2 and I can manually add it without any problem (when added, it does not overwrite other pivot tables or something like that). Also, when I record this with the macro recorder, it gives the same codes as in the macro. Any idea what I might be doing wrong? regards, Hans Sub PivotChangePeriod() Dim PT As PivotTable Dim Sh1 As Sheets Dim wk As Worksheet Set Sh1 = Worksheets(Array('10sheets') For Each wk In Sh1 For Each PT In wk.PivotTables PT.PivotFields("period").PivotItems ("2").Visible = True Next PT Next wk End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to ask for a item# and then show all details for that item | Excel Discussion (Misc queries) | |||
| Recording a macro to change item selected in drop down list | Excel Worksheet Functions | |||
| How to link macro to change an item in drop-down list | Excel Discussion (Misc queries) | |||
| How to use a macro to change the selected item in data validation | Excel Discussion (Misc queries) | |||
| Macro to change the PivotField to sum | Excel Discussion (Misc queries) | |||
Linear Mode
