Ordre des pages avec saut VBA

demongin · 2 Juillet 2010

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 :

- il y a 6 pages,
- 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.

Guest · 2 Juillet 2010

Re : Ordre des pages avec saut VBA

Re,

Merci pour ce retour, j'en prends bonne note.

bon week-end

Effectuez une recherche sur Excel Downloads...

Ordre des pages avec saut VBA

demongin

XLDnaute Nouveau

Guest

Guest

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums