H
Hans
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
(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