Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Weird excel behavior - Print macro 1

Status
Not open for further replies.
ItIsHardToProgram

ItIsHardToProgram

Technical User
Mar 28, 2006
946
CA
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:

Code: 
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.
 
Sort by date Sort by votes
Ok, I get where you were going the first time, controlling the unwanted residence in 1 select case is better.

But will that fix my sorting issue?

Thanks skip,

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


What sorting issue?

If it a matter of the sequence of the Print, Then you'll have to generate a list of unique Regions in the ORDER that you want, and use THAT list as the For...Next to assign the CurrentPage property.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I see, there is no way to define a print order by code?

Also is there a way to print the whole thing in 1 document?, merging all the pages togeter?

Thanks alot skip,

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



Explain what the printing requirement is.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
The first requirement is quite simple, we need a specific order, that is not necesserely alphabetic, hence when we printout looping through pivotitems, we do not have the correct order.

The second requirement, not necessery but quite easier for us, would be to be able to print a whole package togeter, but the sheets need to be dynamic as they are right now.

(only printing from one spreadshit that changes accordingly to the pivot table)

That is because we do monthly PDF packages. But as stated not necessery.


Overall I can of course keep the other code I used before and simply clear cache before and after.

I am simply trying to do this the best way possible.

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.
 
Upvote 0 Downvote





What you do is create a LIST on a separate sheet, of the Régions and a Sequence Number for each.

Then, in your Source Data for the PivotTable, add a column for a Seq Nbr, that you will LOOKUP, using the Région value.

Then for...next on the pvi for the Seq Nbr PivotField, and LOOKUP the Région to assign.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Right, that works, Thank you Skip,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
708
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
remeng
  • Locked
  • Question
Multiple Users in a Macro enabled Excel Doc
Replies
0
Views
311
remeng
remeng
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
417
mintjulep
M
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top