Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Finanical information by Period as a month | Excel Discussion (Misc queries) | |||
How to make pivot table based on two more sheets | Excel Discussion (Misc queries) | |||
How can I make pivot table sub-totals bold? | Excel Discussion (Misc queries) | |||
How do I make a cashflow with pivot table? | Excel Worksheet Functions | |||
How do you set up a Pivot table. Ccan't make it work | Charts and Charting in Excel |