ExcelBanter - Change PivotField Item with macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Change PivotField Item with macro (https://www.excelbanter.com/excel-programming/302685-change-pivotfield-item-macro.html)

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

Change PivotField Item with macro

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

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

.

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

.