Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
I am trying to remove all data from a pivot table. The following code
does not work every time. I don't understand why. Set PT = Worksheets("PT").PivotTables("PT1") With PT 'clear old items On Error Resume Next .PivotFields("Data").Orientation = xlHidden .RowFields(1).Orientation = xlHidden 'add row field .AddFields RowFields:="LocationID" 'add the data filed With .PivotFields("Amount") .Orientation = xlDataField .Function = xlSum End With End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
More Information:
The program works when there are two items in the "Data" area of the pivot table, when there is only one, it doesn't work. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
If you're using Excel 2002 or later version, you can use the following:
'================== Dim pf As PivotField With PT 'clear old items 'On Error Resume Next For Each pf In PT.DataFields pf.Orientation = xlHidden Next pf .RowFields(1).Orientation = xlHidden 'add row field .AddFields RowFields:="LocationID" 'add the data filed With .PivotFields("Amount") .Orientation = xlDataField .Function = xlSum End With End With '============================ C. Roenbaugh wrote: I am trying to remove all data from a pivot table. The following code does not work every time. I don't understand why. Set PT = Worksheets("PT").PivotTables("PT1") With PT 'clear old items On Error Resume Next .PivotFields("Data").Orientation = xlHidden .RowFields(1).Orientation = xlHidden 'add row field .AddFields RowFields:="LocationID" 'add the data filed With .PivotFields("Amount") .Orientation = xlDataField .Function = xlSum End With End With -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Table and Pivot Table dates are not in correct order | Charts and Charting in Excel | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |