Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Field Not Updating Properly (03)

    I have a pivot table whose column field is not updating properly. If I select the dropdown I see some names that are not part of the dynamic range. At one time the items showing were part of the dynamic range.

    I have tried refreshing the database and dropping in the field once again to the pivot table but this did not work.

    Any suggestions would be appreciated.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Field Not Updating Properly (03)

    Try running this macro:

    Sub DelOldPivotItems()
    Dim oPiv As PivotTable
    Dim oField As PivotField
    Dim oItem As PivotItem

    On Error Resume Next

    Set oPiv = ActiveSheet.PivotTables(1)

    For Each oField In oPiv.PivotFields
    If oField.Name <> "Data" Then
    For Each oItem In oField.PivotItems
    If oItem.RecordCount = 0 And Not oItem.IsCalculated Then oItem.Delete
    Next
    End If
    Next

    Set oItem = Nothing
    Set oField = Nothing
    Set oPiv = Nothing
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Field Not Updating Properly (03)

    Hans,

    Thank you for the code. Why would the field not update properly?

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Field Not Updating Properly (03)

    I'm not sure. Excel adds new items to the list, but it doesn't always remove old ones that do not occur any more. The code I posted should remove all unused items.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •