Macro to make period visible in pivot table
 

I have created the macro below to make the current month
(bPeriodNew) visible in a number of Pivot Tables (some 15
in total). However, it doesnt work in some of them and in
most cases I don't know why. In one or two the pivot table
would overlap another one with the new period added and in
these cases I can understand why the macro doesn't run.
But in other Pivot tables everything seems to be OK and
yet the period is not make visible.

The Pivot Tables I use are all linked to external
databases.

Any ideas what might be wrong? Or any suggestions for
another (better) macro?


Regards
Hans

Sub AddPivotPeriod

Dim bPeriodNew As Byte
Dim PT As PivotTable
Dim PI As PivotItem
Dim Sh1 As Sheets
Dim wk As Worksheet

bPeriodNew = Range("period").Value
Set Sh1 = Worksheets(Array("data"))

For Each wk In Sh1
For Each PT In wk.PivotTables
For Each PI In PT.PivotFields"period").PivotItems
On Error Resume Next
If PI.Name = bPeriodNew Then
PI.Visible = True
End If
Next PI
Next PT
Next wk

End Sub

Macro to make period visible in pivot table
 

the first place to look is the statement that would prevent this command
from being executed:

If PI.Name = bPeriodNew Then
PI.Visible = True
End If

You need to put in some debugging statements to see if it passes this test.

--
Regards,
Tom Ogilvy

"Hans" wrote in message
...
I have created the macro below to make the current month
(bPeriodNew) visible in a number of Pivot Tables (some 15
in total). However, it doesnt work in some of them and in
most cases I don't know why. In one or two the pivot table
would overlap another one with the new period added and in
these cases I can understand why the macro doesn't run.
But in other Pivot tables everything seems to be OK and
yet the period is not make visible.

The Pivot Tables I use are all linked to external
databases.

Any ideas what might be wrong? Or any suggestions for
another (better) macro?


Regards
Hans

Sub AddPivotPeriod

Dim bPeriodNew As Byte
Dim PT As PivotTable
Dim PI As PivotItem
Dim Sh1 As Sheets
Dim wk As Worksheet

bPeriodNew = Range("period").Value
Set Sh1 = Worksheets(Array("data"))

For Each wk In Sh1
For Each PT In wk.PivotTables
For Each PI In PT.PivotFields"period").PivotItems
On Error Resume Next
If PI.Name = bPeriodNew Then
PI.Visible = True
End If
Next PI
Next PT
Next wk

End Sub

Macro to make period visible in pivot table
 

This is the error message I get:

"Unable to set the Visible property of the PivotItem class"

however, when if run the macro with an "on error resume
next " statement, it correctly adds the new period to some
of the pivot tables....

-----Original Message-----
the first place to look is the statement that would
prevent this command
from being executed:

If PI.Name = bPeriodNew Then
PI.Visible = True
End If

You need to put in some debugging statements to see if it
passes this test.

--
Regards,
Tom Ogilvy

"Hans" wrote in
message
...
I have created the macro below to make the current month
(bPeriodNew) visible in a number of Pivot Tables (some
15
in total). However, it doesnt work in some of them and
in
most cases I don't know why. In one or two the pivot
table
would overlap another one with the new period added and
in
these cases I can understand why the macro doesn't run.
But in other Pivot tables everything seems to be OK and
yet the period is not make visible.

The Pivot Tables I use are all linked to external
databases.

Any ideas what might be wrong? Or any suggestions for
another (better) macro?


Regards
Hans

Sub AddPivotPeriod

Dim bPeriodNew As Byte
Dim PT As PivotTable
Dim PI As PivotItem
Dim Sh1 As Sheets
Dim wk As Worksheet

bPeriodNew = Range("period").Value
Set Sh1 = Worksheets(Array("data"))

For Each wk In Sh1
For Each PT In wk.PivotTables
For Each PI In
PT.PivotFields"period").PivotItems
On Error Resume Next
If PI.Name = bPeriodNew Then
PI.Visible = True
End If
Next PI
Next PT
Next wk

End Sub



.