Excel Pivot Tables - Dynamically Changing Pivot Table Structure

Hot Topic

Help with comma separated values in report
[Excel] How to convert Excel to jpg, gif image?
OUTLOOK When trying to open Outlook, I get the following:
hidden versus visible in pivot tables
LBound and UBound functions
ISV Software Requirements
How do connect an Excel file from Remote Server
Access 2002 Error 2455 - invalid reference to the property Form/Report
Autofill
List Box Search

Microsoft ISV

How to compare and control in VBA two numeric TextBoxes fields
combine 3 combo boxes values into one string
Adding table in Word Doc over writes previous lines
Need direction on using code instead of VLOOKUP
iexplore.exe - Application Error
Office 10 \ winword.exe runtime error
build a matrix
Microsoft Ecosystem
Moving Data
Loop causing MS Word freeze

Excel Pivot Tables - Dynamically Changing Pivot Table Structure

I have one pivot table which is associated to a Excel Range.

I dynamically change the structure of the pivot table (eg Columns, Row, Data) to create different outputs.

I used the following code to "empty" out the Pivot Table structure

    With Sheets("Pivot").PivotTables("PivotTable1")
        For intLoop = 1 To .PivotFields.Count
            .PivotFields(intLoop).Orientation = xlHidden
        Next
    End With

But in some cases, the Data field was not being cleared at all.

When "empty" out the structure and build a new pivot table structure, it holds on to the Data Field that was not cleared. But when I "empty" out and generate another Pivot Table Structure, on the second time it clears out the Data Field.

I tried referenceing it explicitly with

Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Data").Orientation = xlHidden

On the occasions when it does fail there seems to be no "Data" field present, even though there is !

This only seems to occur when the is Only 1 Data Field present. If there is more than one Data Field then the "empty" out code works OK.

Is there another way to clear out the structure of the pivot table without deleting it and starting again

 




Answer this question

Excel Pivot Tables - Dynamically Changing Pivot Table Structure

  • setareh

    any idea how to ungroup pivotitems or delete a grouped pivotfield Each time I manually ungroup pivotitems, Excel crashes on the last item (no matter the order of my ungrouping). I need to ungroup all items to do the pi.delete method mentioned above. Anyone have any ideas I'd prefer just to delete the grouped pivotfields, but pf.delete doesn't work on these grouped fields.

  • DiamondDavo

    Hi Warren,

    Here's the response from the support engineer:

    The following code should clear the old items from the dropdown lists:

    Sub DeleteOldItemsWB()

    'gets rid of unused items in PivotTable based on MSKB (Q202232)

    'had to go through procedure twice to clear items (for i= 1 to 2)

    Dim ws As Worksheet

    Dim pt As PivotTable

    Dim pf As PivotField

    Dim pi As PivotItem

    Dim i As Integer

    On Error Resume Next

    For i = 1 To 2

    For Each ws In ActiveWorkbook.Worksheets

    For Each pt In ws.PivotTables

    For Each pf In pt.PivotFields

    For Each pi In pf.PivotItems

    pi.Delete

    Next

    Next

    pt.RefreshTable

    Next

    Next

    Next

    End Sub

    =====================================

    The general mechanism for removing unused PivotItems is as follows:

    Sub DeletePivotItem()

    'deletes PivotItem under ActiveCell

    ActiveCell.PivotItem.Delete

    End Sub

    ===

    If the PivotItems you are trying to delete is part of a grouped field, you

    will need to ungroup the field before this will work.

    -brenda (ISV Buddy Team)



  • Excel Pivot Tables - Dynamically Changing Pivot Table Structure
Answer this question