Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table refresh (Excel 97)

    Hello,

    I have the following problem while working with a pivot table. I have the raw data, created the pivot table, and after that changed the descriptions of the products in the raw data, so that they are generic (we want to give it as a 'sample' as opposed to a real set of data. However now, when I refresh the pivot table, it still shows me the old descriptions, which don't exist anymore.
    I am not sure whether I am explaining this very well, so I am attaching here a screen (can't attach the file as it is huge), hopefully you get the idea from there.
    Does anybody know what is going there and how can I fix it?

    Thanks in advance,
    K.
    Attached Images Attached Images

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

    Re: Pivot table refresh (Excel 97)

    <post#= 38971>post 38971</post#> has a routine that removes these unused items. I have copied it here, added declarations for the variables used, and set object variables to Nothing at the end. The routine assumes that your pivot table is the first (or only) pivot table on the sheet.

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

    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
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table refresh (Excel 97)

    Hello,

    I ran your macro, however I get at the line:
    If oItem.RecordCount = 0 And Not oItem.IsCalculated Then oItem.Delete
    a Runtime error 1004:'Application defined or object defined' error

    Unfortunately my knowledge of pivot tables (and macros with this regard) is very limited, so I don't know what to look for...

    Thanks,
    K.

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

    Re: Pivot table refresh (Excel 97)

    I'm sorry, I don't know what's wrong. I have tested this code on a small pivot table I created for this, and later on a large existing pivot table. It works OK there, although it's very slow on a large pivot table. Anyone else with a bright idea?

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table refresh (Excel 97)

    Hans

    I tested it too - and it worked fine for me on 97 SP1. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    I'd be tempted to drop the "and Not olItem.IsCalculated" if there are no calculated fields in the particular table
    or, preceed the line with an "On Error Then Resume Next" statement (as below).

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

    Set oPiv = ActiveSheet.PivotTables(1)

    For Each oField In oPiv.PivotFields
    If oField.Name <> "Data" Then
    On Error Resume Next
    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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table refresh (Excel 97)

    Has anybody read the post 38971 that Hans referred to? I had used this macro as well and ran into the same error. As I explain in the referenced post, my problem was caused by the fact that the pivot table source data included empty rows at the bottom of the range and also we had grouped some fields which caused the macro to fail. Is this possibly your problem as well?

    Does anyone know how to code the macro to avoid the problem with the grouped data? This is a large inconvenience as we perform a lot of groupings in all of our pivot tables. Does the On Error Then Resume Next statement take care of this problem?


    Thanks,

    Brett

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

    Re: Pivot table refresh (Excel 97)

    On Error Resume Next will suppress *all* error messages. It might work OK in this case, but I would test extensively on copies of production spreadsheets before using it for real.

Posting Permissions

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