#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create refresh button in worksheet to refresh Pivot Table Data Ron Excel Worksheet Functions 1 October 13th 07 01:20 AM
Pivot table refresh Richard Ward Excel Discussion (Misc queries) 0 May 21st 07 06:20 PM
refresh a new worsheet on pivot table refresh [email protected] Excel Worksheet Functions 0 February 9th 07 08:39 PM
pivot table will not refresh CN Excel Discussion (Misc queries) 4 September 18th 06 03:34 AM
pivot refresh clears pivot fields? Leejo Excel Discussion (Misc queries) 0 October 13th 05 03:10 PM


All times are GMT +1. The time now is 10:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"