Excel Pivot Table - Clear cache memory 1

[ItIsHardToProgram]

Hello everyone,

I have mentioned this earlier in another post, where we found a fix that seemed to work, but the file decided that it started RECREATING ghosts again, in my data, and my clear cache method dosnt seem to be removing them.

Is there a way to COMPLETLY rewrite cache, before every procedure.

I have a print macro that prints data, thats what seems to be creating the ghosts, there is probably a reason for that, a reason that I don't know.

Please help me this issue is really getting on my wrong side.

Here is the macro to clear cache.

Sub CleanMyPivots()
'SkipVought/2007 Apr 30/817-280-5438
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

Here is what the print macro does:

Sub ImpressionQuebec()

CleanMyPivots

On Error Resume Next
Dim pvi As PivotItem
Dim PviName() As String
Dim NamePVI


Call SortRes(PviName)


If ActiveWorkbook.Sheets("États").Range("$B$2:$B$2").Value = "Fonds I" Then
    Application.ScreenUpdating = False
    With Sheets("États")
            With .PivotTables("EtatsFinancier")
                .PivotFields("Résidence").CurrentPage = "(Tous)"
                .PivotFields("Région").CurrentPage = "(Tous)"
                .PivotFields("VPR").CurrentPage = "Renée"
                Sheets("ÉtatsRésultat").PrintOut Copies:=1, Collate:=True
                
                For Each NamePVI In PviName
                    .PivotFields("Résidence").CurrentPage = NamePVI
                    
                    Select Case NamePVI
                        Case "StGeorges", "Atrium", "Saguenay", "Jonquiere", "Cascades", _
                        "RiveSud", "Archer", "Estrie"
                            Sheets("ÉtatsRésultat").PrintOut Copies:=1, Collate:=True
                            If NamePVI = "Archer" Then Exit Sub
                            'MsgBox (NamePVI)
                        Case Else
                            'Sheets("ÉtatsRésultat").PrintOut Copies:=1, Collate:=True
                            'MsgBox (pvi.Value)
                    End Select
            Next
        End With
    End With
    Application.ScreenUpdating = True
End If

Cleanmypivots
End sub

If any one could help me with this issue I would be very happy, ghost that are created replace data with otehr data (only in the pivot table) and they also create data that doesnt exist, for example, pembroke creates pembroke2, but there is no pembroke2.

ALSO there is a weird parcel of data that represents nothing and appears as a blank.

The data is not reflected in any category of my pivot table.

Im thinking this is corruption and im hoping not.

Can you find me a fix, beside redo the pivot table everytime???

Thank you

Julien,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Maybe this will help giving a diagnosis.

To fix this, this is what I did, Replace all residence name in the specific section that was messed up (this is in the rough data page).

1- Search and replace all --> Pembroke with Pembroke2
etc....

2- Actualised the pivot table

3- Search and replace all --> Pembroke2 with Pembroke

4- checked data, it checked out ok.

Then I tested my print macro, everything was fine, before and after.

Is this behavior corruption?

Thx

Julien,


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

What does SortRes(PviName) do?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Sub SortRes(list() As String)
'tri Manuellement le tableau, en attendant une nouvelle cellule
Dim pvi As PivotItem
Dim PviCount As Integer
PviCount = Sheets("États").PivotTables("EtatsFinancier").PivotFields("Résidence").PivotItems.Count
ReDim list(PviCount)

For Each pvi In Sheets("États").PivotTables("EtatsFinancier").PivotFields("Résidence").PivotItems
    i = i + 1
    list(i) = Sheets("OrdreImpression").Cells(i, 1).Value
Next

End Sub

The behavior was there before the SortRes was there.

The names in SortRes correspond 100% to the names in the Piv. table.

Would you suggest that if he tries to open a pvi that dosnt exist, it would create this "ghost" even though it should only create an error?

Eventualy SortRes will be more dynamic, but until we add a column with the Residence number (wich is in the correct order) there is no way really of doing a better sorting than manually changing the sorting of the data.

Hth figure the problem out,

Thanks skip, your always helpful

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

check things out with this

    Dim pvt As PivotTable, ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each pvt In ws.PivotTables
            MsgBox ws.Name & pvt.TableRange1.Address
        Next
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok, we have 6 pivot tables, with valid range, some of them can probably eliminated, but where would you suggest going from there?



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

How is the source data updated?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

The data itself is only values, no formula or anything, either positiv or negative value.

We have files, in wich we have extracts, 1 per residence to be exact.

This extract gives data with a key / date / residence / fund / category etc...

We simply add new data when each year goes by.

The residence files are for each year, containing the anticipated for the present year and the budget for the next year.

The consolidated file regroups from 2004 to 2009, actual, budget and anticipated for the present year only.

not every residence go up to 2004, some only go up to 08/09

We sometimes change the data, without removing any line, simply changing the column with the values (without removing it).

Recently we have added some columns, but we also rebuilt the pivot table.

Ask me if you need more information...



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Name1, Name2.. occurs when you REUSE a field Name in the same PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Could you clarify that a bit, what do you mean by reuse ?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

If, in your Source Data, you have a duplicate Field Name, it will do you a favor, and name the second occurrence Name2 and the third occurrence Name3, etc.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Right, but in my source data I do not have a duplicate field name, thats the main problem....

Right now im struggling trying to get rid of a Rideau2, while Rideau will not appear, im trying all sorts of modification, like changing name etc.... won't budge....

=/



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

In what field?

Have you used the Watch Window to examine ALL the PivotItem Values in that field?

You can also examine the PROPERTIES of various fields to be sure they are what you expect them to be.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

It is in the field residence.

Yes I have made sure every pivotItem value were correct, and in fact they arent, Rideau does not appear and Rideau2 appears.

Even though the source data marks it as Rideau, and Rideau2 does not exist.

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

There are also ghosts...

How can I use a "Watch Window" to look at the pivot items, through vbase???

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

So in the source data, if you turn on the AutoFilter and look at ALL the unique values in the residence field, there are no values that you do not expect?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Exactly.

This is EXACTLY WHY I am thinking corruption.


Just for testing, this is not working

Dim pvt As PivotTable, ws As Worksheet, pvi As PivotItem
    For Each ws In ActiveWorkbook.Worksheets
        For Each pvt In ws.PivotTables
            For Each pvi In pvt.PivotFields("résidence")
            MsgBox ws.Name & pvt.TableRange1.Address & " " & pvi
            Next
        Next
    Next

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[xlbo]

I have seen this before when items get renamed int he pivot table - it can be VERY hard to get them back to the original - I normally give up and redo the pivot

To be clear - I have seen this happen when a field NAME or ITEM is overtyped with something else...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

well that will work only for tables with a résidence field.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I see, ive tried referencing directly like this

For each pvi in Sheets("États").PivotTables("EtatsFinancier").PivotFields("Résidence")

Still no joy,..

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.