Weird excel behavior - Print macro 1

[ItIsHardToProgram]

I have a print macro that selects filters in a pivot table and then select a sheet to print, then reselects the pivot table and changes filters again.

A weird behavior started appearing.

When I print out with the print macro the first copy comes out fine, but after, when we reselect any filters, the date is screwed up.

For example filter Laviolette follows filter Charlersbourg, well Laviolette will become charlesbourg and etc...

I will put the macro here:

Sub ImpressionQuebec()

'
' ImpressionQuebec Macro
' Macro enregistrée le 14/07/2008 par Julien Roy
'
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Région")
        .PivotItems("Ottawa").Visible = False
        .PivotItems("SWont").Visible = False
        .PivotItems("ROC").Visible = False
        .PivotItems("GTA").Visible = False
        .PivotItems("Corpo").Visible = False
        .PivotItems("Partenariat").Visible = False
    End With
    
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Région"). _
        CurrentPage = "(Tous)"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Fonds"). _
        CurrentPage = "(Tous)"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "(Tous)"
        Sheets("États-Qc").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
  
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "StGeorges"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
     Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Atrium"
    ActiveSheet.Refresh
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Saguenay"
    Sheets("États (2)").Select
    ActiveSheet.Refresh
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Jonquiere"
    
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Cascades"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
     Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "RiveSud"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Estrie"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Archer"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Monaco"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "BBoulogne"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Wellesley"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
      
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Ermitage"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
        Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Pat"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "StJerome"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "RoyalPins"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "NDame"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "SteFoy"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Laviolette"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Ecores"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    

    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Chicoutimi3"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    

 
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Harmonie2"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
       
    Sheets("États").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "ChicoutimiAG2"
    Sheets("États (2)").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
    Sheets("États").Select
    
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Région")
        .PivotItems("Ottawa").Visible = True
        .PivotItems("SWont").Visible = True
        .PivotItems("ROC").Visible = True
        .PivotItems("GTA").Visible = True
        .PivotItems("Corpo").Visible = True
    End With
        
End Sub

Thanks for your help, this is a very urgent mather and we are looking for a fix.

A hint on how to bring back the data back to normal else than rebuilding the Pivot table would help.

Could it be corruption?


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

 

[xlbo]

when we reselect any filters, the date is screwed up

Can you give any more detail about HOW exactly the data/date is screwed up?

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

 

[ItIsHardToProgram]

Can you give any more detail about HOW exactly the data/date is screwed up?

I believe there is indeed more detail to give.

The sheet printed out has cells that are referenced like following:

=LIREDONNEESTABCROISDYNAMIQUE("Annuel",États!$A$7,"Ligne","Loyers","Livre","REEL","Année",2005,"Catégorie","A- Revenus","Numéro","A01")

Know that LIRESDONNESTABCROISDYNAMIQUE is to get data from a pivot table, simply in french.



WHen I execute the macro, that you have seen, most of the sheet print out fine, but at some random breakpoints, they print the data from the previous sheet, with the filter of the new sheet. For example, taking the code I have put in the last post.

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "ChicoutimiAG2"

Will become

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = "Harmonie2"

And thus permanantly, only way to fix is to rebuild the pivot table.

When i select the data, the data is fine, but the pivot table reads ChicoutimiAG2 as Harmonie2. Data coludes somehow.

This is a very weird behavior that do not seem like a corruption, since it is not random.

**NOTE** The titles are always fine, the data is with the wrong title(filter)

It would seem that this happens when theres a missing reference, for example for all the cell in the sheet the reference is missing i.e. there would be no data for ChicoutimiAG2, but ALL the filters that come after are delayed.

I hope this made the issue clearer.

I thought of fixing with a IF(ISERROR()), but I did not have time to test. Then again I would like a better fix or an explanation at least...

Thank you for taking the time to understand my issue.

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

 

[ItIsHardToProgram]

It would seem that this happens when theres a missing reference, for example for all the cell in the sheet the reference is missing i.e. there would be no data for ChicoutimiAG2, but ALL the filters that come after are delayed.

Upon reading I will rephrase.

When the reference in the cells of the sheet im trying to print is missing, it seems to create a breakpoint that shifts data from one filter to another, some sort of delay...

i.e. ChicoutimiAG2's cell appear as #REF! the first time, then, ChicoutimiAG2 cell appear with harmonie2's data and harmonie2 with ChicoutmiAG2 and so on. Is this clearer? I really can't phrase this since I don't understand the behavior...

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

 

[ItIsHardToProgram]

Am I in the wrong forum or is no one familiar with this behavior?

Maybe its the vacation...

Have a nice weekend every one.

Cordialy,

Julien

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

 

[xlbo]

Hi - you are in the right forum but it has been the weekend!

1st thing to try:

activesheet.pivottables(1).pivotcache.missingitemslimit = xlmissingitemslimitnone

This clears the pivot cache of all missing data

If that does nothing then we will look at the usage fo the GetPivotdata Function which is what seems to be giving you trouble

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

 

[ItIsHardToProgram]

At what point in the code should I use this, should I maybe use it as a breakpoint for each print?

Thank you for your time.

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

 

[ItIsHardToProgram]

The problem still persists...

Ive introduced the code after each Sheets("États").Select

Julien,

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

 

[SkipVought]

Could it be that you are assigning the CurrentPage property a value that does not exist?

Your code could be cleaned up quite a bit if you were to run Geoff's missing item code first and then just do a pivot on Résidence to get a list of valid PivotItems.

Then loop thru that list to get the values to assign. Your code could be reduced to about 3-4 dozen lines.

Skip,

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

 

[ItIsHardToProgram]

So I should find out wich résidence I have before trying to reference them, missing reference could cause that problem?

Maybe you could get me started, how do you get a list of thje valid pivotitems?

dim x as string
x = ActiveSheet.Pivottable.pivotitems("Résidence").value

???

Afterward I would do something like

For x to ChicoutimiAG3
Sheet("États").Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Résidence"). _
        CurrentPage = x

Next x

I dont believe that would work though, I need some help.

for your question as if it could be a missing reference causing the problem I would seriously doubt it since when I do not print out the sheet the problem does not reveal itself, its only when I printout the selection that the data gets messed up...

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

 

[SkipVought]

On another sheet just do a pivot table on Résidence.

Here's the code that I use to clean ALL pivot tables...

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

So here's a sample of what you could do...

dim pvi as pivotitem
for each pvi in NewCheetObject.NewPivotTableObject.").PivotFields("Résidence").PivotItems
   With Sheets("États")
      With .PivotTables("Tableau croisé dynamique1")
        .PivotFields("Résidence").CurrentPage = pvi.value
        select case pvi.value
          case "(Tous)"
             Sheets("États-Qc").PrintOut Copies:=1, Collate:=True
          case else
             Sheets("États (2)").PrintOut Copies:=1, Collate:=True
        end select
   
Next

Skip,

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

 

[ItIsHardToProgram]

Very nice way of cleaning that code mess I had, dint really have the time to work out my code, thanks

Now for my issue, you also think clearing the cash should have a positive effecet on my data?

Thanks,

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

 

[ItIsHardToProgram]

I need also an information that could help, how do I CLEAN the macro references, we have alot of files and each new file has references (without having a link) to macros in other files, wich is extremly annoying, how can I clean the macro library???

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

 

[ItIsHardToProgram]

for each pvi in NewSheetObject.NewPivotTableObject.PivotFields("Résidence").PivotItems

I get a missing object error on this code, why is this?


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

 

[ItIsHardToProgram]

Ok, I have worked the formula, but Im still having trouble handling wich pivotitems I want to print and others i don't want.

Heres my code.

Sub ImpressionQuebectest()

CleanMyPivots
With ActiveSheet.PivotTables("EtatsFinancier").PivotFields( _
        "Région")
        .PivotItems("Ottawa").Visible = False
        .PivotItems("SWont").Visible = False
        .PivotItems("ROC").Visible = False
        .PivotItems("GTA").Visible = False
        .PivotItems("Corpo").Visible = False
        .PivotItems("Partenariat").Visible = False
        .PivotItems("#N/A").Visible = False
End With
Dim pvi As PivotItem
Dim pvi2 As PivotItem
For Each pvi In ActiveSheet.PivotTables(1).PivotFields("Résidence").PivotItems
   With Sheets("États")
      With .PivotTables("EtatsFinancier")
        .PivotFields("Résidence").CurrentPage = pvi.Value

        Select Case pvi.Value
          Case "(Tous)"
             Sheets("États-Qc").PrintOut Copies:=1, Collate:=True
          Case Else
             For Each pvi2 In ActiveSheet.PivotTables(1).PivotFields("Région").PivotItems
                Sheets("États").PivotTables("EtatsFinancier").PivotFields("Région").CurrentPage = pvi2.Value
                        Select Case pvi2.Value
                            Case Ottawa
                            Case Swont
                            Case ROC
                            Case GTA
                            Case Corpo
                            Case Partenariat
                            Case "#N/A"
                            Case Else
                                Sheets("États (2)").PrintOut Copies:=1, Collate:=True
                            End Select
                      
            Next
        End Select
      End With
     End With
Next
With ActiveSheet.PivotTables("EtatsFinancier").PivotFields( _
        "Région")
        .PivotItems("Ottawa").Visible = True
        .PivotItems("SWont").Visible = True
        .PivotItems("ROC").Visible = True
        .PivotItems("GTA").Visible = True
        .PivotItems("Corpo").Visible = True
        .PivotItems("Partenariat").Visible = True
End With
CleanMyPivots

End Sub

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

 

[SkipVought]

In the Select Case structure, you can list all the Values you want to include in one Case...

    Select Case pvi2.Value
        Case "Ottawa", "Swont", "ROC"
            'no printout
        Case "Dallas", "Fort Worth", "Houston"
            Sheets("Texas Cities").PrintOut Copies:=1, Collate:=True
        Case Else
            Sheets("États (2)").PrintOut Copies:=1, Collate:=True
    End Select

Skip,

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

 

[ItIsHardToProgram]

Sheets("États").PivotTables("EtatsFinancier").PivotFields("Région").CurrentPage = pvi2.Value

This triggers an error saying the pivot items dont support the default_ property, Why would that be?

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

 

[ItIsHardToProgram]

error 1004"

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

 

[ItIsHardToProgram]

How about I make this more clear to you,

in my Pivot Table, there are 3 filters wich deal with the selection of a residence, first of all there are the funds, fund I to IV, after the region and finaly the name.

Basicaly, for Quebec, I need all funds, some region, all residences included in "quebec".

What would be the best way to proceed?

There is also a macro per fund and a macro for ROC etc...


There is a certain order that we respect in our presentations, I need that order to be reflected in the printouts.


1.Presently the code doesnt necesserely print the right residence with the right region.

2.Also, hiding the pivot items doesnt remove them from the cache, could I instead remove them from the cache?

3. Is there a way to sort the cache so that it reflects the order I want to give them?

Thanks for your help skip,



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

 

[SkipVought]

You can really do this WITHOUT the NEW sheet...

   With Sheets("États")
        With .PivotTables("EtatsFinancier")
            For Each pvi In .PivotFields("Résidence").PivotItems
                .PivotFields("Résidence").CurrentPage = pvi.Value
        
                Select Case pvi.Value
                    Case "(Tous)"
                       Sheets("États-Qc").PrintOut Copies:=1, Collate:=True
                    Case "Ottawa", "Swont", "ROC", "GTA", "Corpo", "Partenariat", "#N/A"
                        'no printout
                    Case Else
                        Sheets("États (2)").PrintOut Copies:=1, Collate:=True
                End Select
            Next
        End With
    End With

Skip,

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