Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Refresh
I'm trying to write a macro to remove all of the fields that are in the
layout area of my pivot table. I found a macro online that did something similar and I adapted it to look like this: Sub PivotRefresh() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields pf.Orientation = xlHidden Next pf End Sub The problem, however, is that the macro above cycles through each pivot field in the pivot field list even though the data items are not named the same way as their respective fields (i.e. Sum of.../Count of.../etc.). The result is that the macro gives an error message when it tries to remove the field that is in the data area. Is there any way that I can make this macro work? Please help. Thanks, Michael |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Refresh
On Jul 3, 1:12*pm, MichaelR
wrote: I'm trying to write a macro to remove all of the fields that are in the layout area of my pivot table. I found a macro online that did something similar and I adapted it to look like this: Sub PivotRefresh() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) * For Each pf In pt.PivotFields * * * pf.Orientation = xlHidden * Next pf End Sub The problem, however, is that the macro above cycles through each pivot field in the pivot field list even though the data items are not named the same way as their respective fields (i.e. Sum of.../Count of.../etc.). The result is that the macro gives an error message when it tries to remove the field that is in the data area. Is there any way that I can make this macro work? Please help. Thanks, Michael Hi Michael, what is the error message? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Refresh
The error message says: "Unable to set the orientation property of the
PivotField Class" Sometimes the macro actually clears all fields (including the data fields) and sometimes this message pops up and the field in the data item will still be there. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
Pivot table refresh | Excel Discussion (Misc queries) | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
pivot table will not refresh | Excel Discussion (Misc queries) | |||
pivot refresh clears pivot fields? | Excel Discussion (Misc queries) |