Pivot Table Duplicate Field Items

[KMacattack]

Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This is a two part questions, <o></o>
1) In my pivot tables there are duplicate items in the fields. It retrieves the data from an Access Database. <o></o>
and when I go to select a month it will list Jan, Jan, Feb, Feb and so on. In addition there are many formulas within the pivot table. so if there is an easy was to fix it without recreating the pivot tables and recreating the formulas that would be great.
and <o></o>
2) I have a macro that will go through each one of these pivot tables and set each item visible. and when there are duplicates it seems that it can't set the visible property to the pivot item. the code is below to set all the items visible.

For Each PvtTbl In Sheets("Pivot").PivotTables
   For Each Pi In PvtTbl.PivotFields("Month").PivotItems
       Pi.Visible = True
   Next
Next<o:p></o:p>

<o></o>
Any Help would be great,
Thank you

 

[ZVI]

Hi,

Try this to delete old items from all pivot fields:

<font face=Courier New>
Sub RefreshAllPivots()
  Dim x
  For Each x In ActiveWorkbook.PivotCaches
    x.MissingItemsLimit = xlMissingItemsNone
    x.Refresh
  Next
End Sub</FONT>

 

[KMacattack]

Thank you, That worked to delete old items from the pivot fields.

though I am still having problems with the set visible property.

i even recorded a macro to change which fields were selected to be viewed in the row part of the pivot table and even that is giving me an error.
"Run-Time error '1004':
Unable to set the Visible Property of the Pivot Item Class"

thank you

 

[ZVI]

Yes, there is a trick in using of manual sorting before and reset it after the filter items manipulation.
Try this example taking into account the info in comments of the code:

<font face=Courier New>
' ZVI:2009-02-05 http://www.mrexcel.com/forum/showthread.php?p=1828905
' To set Pivot Field Filter correctly it is required:
' - manual autosorting mode of a field (it is not widely known!)
' - firstly setting the reqired items of a field to on
' - secondly setting other items to off
' Example: let's assume that PVT has field "Month" with numeric data: 1,2,...,12
Sub SetFieldFilter()
  Dim PvtField As PivotField, PvtItem As PivotItem
  Dim SortOrder, SortField
  Dim MonthNum As String
  Const Fld = "Month"     ' <-- month field name of the Pivot Table
  MonthNum = Month(Date)  ' <-- the current month number to be set On
  ' Set PVT filter item
  With ActiveSheet.PivotTables(1)
    ' Switch to manual updating
    .ManualUpdate = True
    ' Save the sorting parameters
    SortOrder = .PivotFields(Fld).AutoSortOrder
    SortField = .PivotFields(Fld).AutoSortField
    ' Set sort order of a field to manual !!!
    If SortOrder <> xlManual Then .PivotFields(Fld).AutoSort xlManual, SortField
    ' Switch On the required item in a field
    .PivotFields(Fld).PivotItems(MonthNum).Visible = True
    ' Then switch Off other items
    For Each PvtItem In .PivotFields(Fld).PivotItems
      If PvtItem.Name <> MonthNum Then PvtItem.Visible = False
    Next
    ' Restore the field sort order
    If SortOrder <> xlManual Then .PivotFields(Fld).AutoSort SortOrder, SortField
    ' Switch to auto updating
    .ManualUpdate = False
  End With
End Sub</FONT>

Regards,
Vladimir

 

[KMacattack]

Everything is working now.
Thank you for you help.

 

[ZVI]

Good news, I'm glad it helped!

 

[qwertytoo]

I just want to say that the first piece of code posted by ZVI worked for me. It did require a couple more steps to completely clear out the problems I was seeing. That code changes a setting on the pivot table (which doesn't seem to be linked to any option setting visible in Excel) but doesn't actually delete the items which were causing me the problem.

Basically the source data I had changed in a way that Excel created a new item with a different name, for something that had the same source data. I had a data source that originally contained "January" but somehow when it updated, the pivot thought that the new set of data had a different January, so it created an entry called "January2".

Running ZVI's code eliminated the original "January" but it didn't rename "January2" since that data isn't missing - it finds its source data "January" in the current data set.

I had to modify the source data so that "January" didn't appear in the data, refresh the pivot (removing the missing item) and then restoring the data and refreshing the pivot finally got "January" back into the correct place.

Of course, for stand-alone pivot tables, it would normally be easier to delete the pivot table and start again. This procedure is only necessary if the pivot tables are complex, embedded within other data on the sheet or you have several tables based off a common pivot cache.