1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
| Option Explicit
Private SautDePageDuHaut As Long
Private SautDePageDuBas As Long
Private NbFactures As Long
Private DerniereLigneFactures As Long
Private Pb As HPageBreak
Private FactureAZero As Boolean
Private MatriceImpression() As Variant
Private AireAImprimer As Range
Private ShAImprimer As Worksheet
Sub ImprimerSansLesFacturesAZero()
' Message dans Développez.com
' http://www.developpez.net/forums/d1342967/excel/impression-avec-saut-page-cellule-a-0/#post7288979
Dim ColonneTotalRemit As Long
Dim LigneTotalRemit As Long
Dim I As Long
Dim Cellule As Range
Dim AireABalayer As Range
Dim RestitutionSautsDePage As String
Set ShAImprimer = ActiveSheet
ColonneTotalRemit = 7 ' Colonne G
NbFactures = 0
DerniereLigneFactures = Cells(ActiveSheet.Rows.Count, ColonneTotalRemit).End(xlUp).Row + 1
ReDim MatriceImpression(3, NbFactures) ' 0 : numéro, 1 : Ligne du haut, 2 : Ligne du bas, 3 : A imprimer ou rien
Set AireABalayer = Columns(ColonneTotalRemit).Cells
' Recherche des factures
'-----------------------
For Each Cellule In AireABalayer
If Cellule = "TOTAL REMIT" Then
LigneTotalRemit = Cellule.Row
If Cellule.Offset(0, 1) > 0 Then
Call RepererLesSautsDePageEncadrantUneFacture(LigneTotalRemit, True, DerniereLigneFactures)
Else
Call RepererLesSautsDePageEncadrantUneFacture(LigneTotalRemit, False, DerniereLigneFactures)
End If
NbFactures = NbFactures + 1
ReDim Preserve MatriceImpression(3, NbFactures)
End If
Next Cellule
I = 0
For NbFactures = LBound(MatriceImpression, 2) To UBound(MatriceImpression, 2)
RestitutionSautsDePage = RestitutionSautsDePage & MatriceImpression(0, I) & " " & MatriceImpression(1, I) & " " & MatriceImpression(2, I) & " " & MatriceImpression(3, I) & Chr(10)
I = I + 1
Next NbFactures
MsgBox (RestitutionSautsDePage)
' Impression des factures
'------------------------
For I = LBound(MatriceImpression, 2) To UBound(MatriceImpression, 2)
If MatriceImpression(3, I) = "A imprimer" Then
Set AireAImprimer = Range(Cells(MatriceImpression(1, I), 1), Cells(MatriceImpression(2, I), ColonneTotalRemit + 1))
' MsgBox ("Facture numéro : " & MatriceImpression(0, I) & " Adresse : " & AireAImprimer.Address & " " & MatriceImpression(3, I))
Call ImprimerLaFacture(AireAImprimer)
If I = 3 Then Exit Sub
Set AireAImprimer = Nothing
End If
Next I
Set AireABalayer = Nothing
Set ShAImprimer = Nothing
End Sub
Sub RepererLesSautsDePageEncadrantUneFacture(LigneFacture As Long, TypeFacture As Boolean, LigneFinDePage As Long)
Dim CompteurSautDePage As Long
SautDePageDuHaut = 1
SautDePageDuBas = 0
CompteurSautDePage = 0
For Each Pb In Worksheets(1).HPageBreaks
SautDePageDuBas = Pb.Location.Row - 1
If LigneFacture < SautDePageDuBas Then
MatriceImpression(0, NbFactures) = NbFactures + 1
MatriceImpression(1, NbFactures) = SautDePageDuHaut
MatriceImpression(2, NbFactures) = SautDePageDuBas
If TypeFacture = True Then MatriceImpression(3, NbFactures) = "A imprimer"
Exit Sub
End If
CompteurSautDePage = CompteurSautDePage + 1
SautDePageDuHaut = Pb.Location.Row
Next Pb
If CompteurSautDePage = Worksheets(1).HPageBreaks.Count Then
MatriceImpression(0, NbFactures) = NbFactures + 1
MatriceImpression(1, NbFactures) = SautDePageDuHaut
MatriceImpression(2, NbFactures) = LigneFinDePage
If TypeFacture = True Then MatriceImpression(3, NbFactures) = "A imprimer"
End If
End Sub
Sub ImprimerLaFacture(AireImpression As Range)
AireAImprimer.Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = AireImpression.Address
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.196850393700787)
.BottomMargin = Application.InchesToPoints(0.196850393700787)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ActiveSheet.PageSetup.PrintArea = ""
End Sub |
Partager