Manipulating Pivot Tables With Macros
 

I have been trying to automate a pivot table to produce 2
standard reports from a database in a separate
spreadsheet. There are three field items which differ
between the reports. I have recorded a macro to hide the
fields and another to show them. Looking at the code
produced by these it appears simple to set the VISIBLE
property for the items to either FALSEor TRUE to show or
hide these items but although the code for hiding the
items works the macro gives an error when trying to set
the VISIBLE property to TRUE. The error is:

Run-time error 1004
Unable to setthe Visible property of the PivotItem class

An extract of the affected code is below:

With ActiveSheet.PivotTables("Rupee").PivotFields
("Activity No")
.PivotItems("14").Visible = False
.PivotItems("14.1").Visible = True
End With

Only the second one (14.1) gives the error.
Has anyone else had a similar problem and is there a
solution?

Manipulating Pivot Tables With Macros
 

Mike..

You always need 1 visible item... so do the TRUE before the FALSE.

You're working with a collection object where the KEY is in fact the
string representation of a number...

If you're working in an international environment,you cannot assume the
user is using the the "." as the decimal separator.

Try following to be safe:

With ActiveSheet.PivotTables("Rupee").PivotFields("Acti vitity No")
.PivotItems(CStr(14.1)).Visible = True
.PivotItems(CStr(14)).Visible = False
End With


HTH,

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Mike Barron" wrote:

I have been trying to automate a pivot table to produce 2
standard reports from a database in a separate
spreadsheet. There are three field items which differ
between the reports. I have recorded a macro to hide the
fields and another to show them. Looking at the code
produced by these it appears simple to set the VISIBLE
property for the items to either FALSEor TRUE to show or
hide these items but although the code for hiding the
items works the macro gives an error when trying to set
the VISIBLE property to TRUE. The error is:

Run-time error 1004
Unable to setthe Visible property of the PivotItem class

An extract of the affected code is below:

With ActiveSheet.PivotTables("Rupee").PivotFields
("Activity No")
.PivotItems("14").Visible = False
.PivotItems("14.1").Visible = True
End With

Only the second one (14.1) gives the error.
Has anyone else had a similar problem and is there a
solution?

Manipulating Pivot Tables With Macros
 

I tried this and it still gave the same error. In any case
I have set the international setting to UK so I know
that '.' is a decimal separator.

More help please
-----Original Message-----
Mike..

You always need 1 visible item... so do the TRUE before
the FALSE.

You're working with a collection object where the KEY is
in fact the
string representation of a number...

If you're working in an international environment,you
cannot assume the
user is using the the "." as the decimal separator.

Try following to be safe:

With ActiveSheet.PivotTables("Rupee").PivotFields
("Activitity No")
.PivotItems(CStr(14.1)).Visible = True
.PivotItems(CStr(14)).Visible = False
End With


HTH,

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Mike Barron" wrote:

I have been trying to automate a pivot table to produce
2
standard reports from a database in a separate
spreadsheet. There are three field items which differ
between the reports. I have recorded a macro to hide
the
fields and another to show them. Looking at the code
produced by these it appears simple to set the VISIBLE
property for the items to either FALSEor TRUE to show
or
hide these items but although the code for hiding the
items works the macro gives an error when trying to set
the VISIBLE property to TRUE. The error is:

Run-time error 1004
Unable to setthe Visible property of the PivotItem class

An extract of the affected code is below:

With ActiveSheet.PivotTables("Rupee").PivotFields
("Activity No")
.PivotItems("14").Visible = False
.PivotItems("14.1").Visible = True
End With

Only the second one (14.1) gives the error.
Has anyone else had a similar problem and is there a
solution?


.

Manipulating Pivot Tables With Macros
 

Mike

Check to make sure that the FIELD "Rupee" VISIBLE at the moment you set
the items.. else please post a longer snippet.

Note that international setting is a USER option NOT saved with the
workbook.


keepITcool


"Mike Barron" wrote:

I tried this and it still gave the same error. In any case
I have set the international setting to UK so I know
that '.' is a decimal separator.

More help please

Manipulating Pivot Tables With Macros
 

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


Mike Barron wrote:
I have been trying to automate a pivot table to produce 2
standard reports from a database in a separate
spreadsheet. There are three field items which differ
between the reports. I have recorded a macro to hide the
fields and another to show them. Looking at the code
produced by these it appears simple to set the VISIBLE
property for the items to either FALSEor TRUE to show or
hide these items but although the code for hiding the
items works the macro gives an error when trying to set
the VISIBLE property to TRUE. The error is:

Run-time error 1004
Unable to setthe Visible property of the PivotItem class

An extract of the affected code is below:

With ActiveSheet.PivotTables("Rupee").PivotFields
("Activity No")
.PivotItems("14").Visible = False
.PivotItems("14.1").Visible = True
End With

Only the second one (14.1) gives the error.
Has anyone else had a similar problem and is there a
solution?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html