Bonjour la communauté,
Je rencontre une petite bizarerie avec une partie de mon code.
Je permet à l'utilisateur via des boutons d'afficher ou masquer des plages de cellules formant des pages A4 pour l'impression.
J'ai donc créé des macros en se sens et pour chaque cas de figure, j'ai automatisé la mise à jour des zones d'impressions comme des sauts de page.
Tout fonctionne à merveille, sauf que dans un cas de figure, l'ordre des pages ne suit plus la logique, c'est à dire ici, de gauche à droite et haut en bas.
Rien de rédhibitoire en soi, car il suffirait de remettre les pages dans l'ordre après l'impression.
Cela se présente ainsi :
Je devrais donc obtenir cela :
[ 1 - 2 ] [ 3 - 4 ]
[ 5 - 6 ]
Or j'obtiens :
[ 3 - 4 ] [ 5 - 6 ]
[ 1 - 2 ]
et dans les cas suivants, pas de pb :
[ 1 - 2 ] [ 3 - 4 ]
ou :
[ 1 - 2 ]
[ 3 - 4 ]
ou encore seulement :
[ 1 - 2 ]
Y a t-il un ordre à respecter dans la désignation des sauts de page, ou même entre colonne et ligne ?
Ce phénomène me laisse pantois....!
Ci-dessous le code de ma macro Saut de Pages (SDP) ;
Il est long car le principe énoncé dans le premier post est reproduit sur de nombreuses pages, mais sans qu'il y ait de problème sur ces dernières.
Le soucis se produit uniquement sur la plage A79 - GJ201.
Merci encore pour l'aide que vous êtes susceptible de m'apporter.
Je rencontre une petite bizarerie avec une partie de mon code.
Je permet à l'utilisateur via des boutons d'afficher ou masquer des plages de cellules formant des pages A4 pour l'impression.
J'ai donc créé des macros en se sens et pour chaque cas de figure, j'ai automatisé la mise à jour des zones d'impressions comme des sauts de page.
Tout fonctionne à merveille, sauf que dans un cas de figure, l'ordre des pages ne suit plus la logique, c'est à dire ici, de gauche à droite et haut en bas.
Rien de rédhibitoire en soi, car il suffirait de remettre les pages dans l'ordre après l'impression.
Cela se présente ainsi :
- il y a 6 pages,
- fonctionnant 2 par 2,
- 4 sur un même niveau, 2 autres en dessous.
- fonctionnant 2 par 2,
- 4 sur un même niveau, 2 autres en dessous.
Je devrais donc obtenir cela :
[ 1 - 2 ] [ 3 - 4 ]
[ 5 - 6 ]
Or j'obtiens :
[ 3 - 4 ] [ 5 - 6 ]
[ 1 - 2 ]
et dans les cas suivants, pas de pb :
[ 1 - 2 ] [ 3 - 4 ]
ou :
[ 1 - 2 ]
[ 3 - 4 ]
ou encore seulement :
[ 1 - 2 ]
Y a t-il un ordre à respecter dans la désignation des sauts de page, ou même entre colonne et ligne ?
Ce phénomène me laisse pantois....!
Ci-dessous le code de ma macro Saut de Pages (SDP) ;
Il est long car le principe énoncé dans le premier post est reproduit sur de nombreuses pages, mais sans qu'il y ait de problème sur ces dernières.
Le soucis se produit uniquement sur la plage A79 - GJ201.
Code:
Sub SautDePage()
'
' Saut De Page Macro
' Macro enregistrÈe le 22/06/2010
' Touche de raccourci du clavier: Ctrl+w
'
ActiveSheet.Unprotect Password:="good"
'Bloque le recalcul automatique ‡ chacune des opÈration
Application.Calculation = xlCalculationManual
'Fige l'Ècran pendant l'exÈcution des macros en arriËre plan
Application.ScreenUpdating = False
'Supprime tous les sauts de page (pas ceux dÈlimitÈs par la zone d'impression)
ActiveSheet.ResetAllPageBreaks
'Met en place du saut de page vertical
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Columns(49)
'Met en place des sauts de page de la partie sous-traitant sans rÈvision
If Range("CS2").Value = "0" And Range("CS3").Value = "1" Then
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Columns(97)
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Columns(145)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(265)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(327)
End If
'Met en place du saut de page 2 conditions : rÈvision + sous traitants
If Range("CS2").Value = "1" And Range("CS3").Value = "1" Then
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Columns(97)
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Columns(145)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(265)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(327)
End If
'Met en place des sauts de page de la partie dqe
If Range("Av5").Value = "2" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ElseIf Range("Av5").Value = "3" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ElseIf Range("Av5").Value = "4" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ElseIf Range("Av5").Value = "5" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ElseIf Range("Av5").Value = "6" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ElseIf Range("Av5").Value = "7" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ElseIf Range("Av5").Value = "8" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ElseIf Range("Av5").Value = "9" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ElseIf Range("Av5").Value = "10" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ElseIf Range("Av5").Value = "11" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ElseIf Range("Av5").Value = "12" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ElseIf Range("Av5").Value = "13" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ElseIf Range("Av5").Value = "14" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ElseIf Range("Av5").Value = "15" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(781)
ElseIf Range("Av5").Value = "16" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(781)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(719)
ElseIf Range("Av5").Value = "17" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(781)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(719)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(657)
ElseIf Range("Av5").Value = "18" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(781)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(719)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(657)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(595)
ElseIf Range("Av5").Value = "19" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(781)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(719)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(657)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(595)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(533)
ElseIf Range("Av5").Value = "20" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1587)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1525)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1463)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1401)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1339)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1277)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1215)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1153)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1091)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1029)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(967)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(905)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(843)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(781)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(719)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(657)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(595)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(533)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(471)
End If
'Met en place du saut de page de la partie avenant 1 ‡ 2
If Range("CS4").Value = "2" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1649)
End If
'Met en place du saut de page de la partie avenant 1 ‡ 5
If Range("CS4").Value = "1" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1649)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(1711)
End If
'LibËre l'Ècran aprËs l'exÈcution des macros en arriËre plan
Application.ScreenUpdating = True
'DÈbloque le recalcul automatique ‡ chacune des opÈration
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Protect Password:="good", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Range("A1").Select
End Sub
Merci encore pour l'aide que vous êtes susceptible de m'apporter.