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
| Dim pb As Object
Dim Cpb As Range, C As Range
Dim I As Byte, j As Byte, k As Byte
Dim ReportSTe As Double, ReportSTf As Double
Dim Last As Integer
'On Error Resume Next
'=====================================***Partie 1 : Suppression des sous-totaux=============================================================
With Feuil2.Range("A2:A" & Range("A" & Application.Rows.Count).End(xlUp).Row)
Do
Set C = .Find("Total")
If Not C Is Nothing Then
Feuil2.Cells(C.Row, "A").EntireRow.Delete
End If
Loop While Not C Is Nothing
End With
'***Partie 2 : Définition auto de la zone d'impression
Feuil2.PageSetup.PrintArea = "$A$2:" & Range("E" & Application.Rows.Count).End(xlUp).Address '"$F$52"
'===========================================***Partie 3 : gestion des sauts de page ======================================================
For Each pb In Feuil2.HPageBreaks
I = I + 1 '***incrémente le n°de saut de page général(Permet de gérer le cas de sauts de pages externes à la zone d'impression)
If pb.Extent = xlPageBreakPartial Then
j = j + 1 '***incrémente le n°de saut de page de la zone d'impression
Set Cpb = Feuil2.HPageBreaks(I).Location
If Cpb.Value <> "Report Sous-Total" Then
Feuil2.Range(Feuil2.Cells(Cpb.Row - 1, Cpb.Column), Feuil2.Cells(Cpb.Row, Cpb.Column)).EntireRow.Insert (xlShiftDown)
Feuil2.Cells(Cpb.Row - 3, Cpb.Column) = "Sous-Total"
If j = 1 Then
Feuil2.Cells(Cpb.Row - 3, "C").Formula = "=SUM(C2:C" & Cpb.Row - 4 & ")"
Feuil2.Cells(Cpb.Row - 3, "D").Formula = "=SUM(D2:D" & Cpb.Row - 4 & ")"
Feuil2.Cells(Cpb.Row - 3, "E").Formula = "=SUM(E2:E" & Cpb.Row - 4 & ")"
With Feuil2.Range(Feuil2.Cells(Cpb.Row - 3, "A"), Feuil2.Cells(Cpb.Row - 2, "E"))
.Interior.ColorIndex = 40
.Font.Bold = True
End With
Else
k = WorksheetFunction.Max(9, Feuil2.HPageBreaks(I - 1).Location.Row)
Feuil2.Cells(Cpb.Row - 3, "C").Formula = "=SUM(C" & k & ":C" & Cpb.Row - 4 & ")"
Feuil2.Cells(Cpb.Row - 3, "D").Formula = "=SUM(D" & k & ":D" & Cpb.Row - 4 & ")"
Feuil2.Cells(Cpb.Row - 3, "E").Formula = "=SUM(E" & k & ":E" & Cpb.Row - 4 & ")"
With Feuil2.Range(Feuil2.Cells(Cpb.Row - 3, "A"), Feuil2.Cells(Cpb.Row - 2, "E"))
.Interior.ColorIndex = 40
.Font.Bold = True
End With
End If
Feuil2.Cells(Cpb.Row - 2, Cpb.Column) = "Report Sous-Total"
Feuil2.Cells(Cpb.Row - 2, "C") = Feuil2.Cells(Cpb.Row - 3, "C")
Feuil2.Cells(Cpb.Row - 2, "D") = Feuil2.Cells(Cpb.Row - 3, "D")
Feuil2.Cells(Cpb.Row - 2, "E") = Feuil2.Cells(Cpb.Row - 3, "E")
End If
End If
Next
'=====================================***Partie 4 : Affichage du total bas de page ====================================================
Last = Feuil2.Range("A" & Application.Rows.Count).End(xlUp).Row
If Feuil2.Cells(Last, "A") <> "Total Général" Then
Feuil2.Cells(Last, "A").EntireRow.Insert (xlShiftDown) '**Permet d'étendre la zone d'impression
Feuil2.Range(Feuil2.Cells(Last + 1, "A"), Feuil2.Cells(Last + 1, "E")).Copy (Feuil2.Cells(Last, "A"))
Feuil2.Cells(Last + 1, "A").EntireRow.ClearContents
Feuil2.Cells(Last + 1, "A") = "Total Général"
Feuil2.Cells(Last + 1, "C") = "=SUM(C" & WorksheetFunction.Max(9, Cpb.Row - 2) & ":C" & Last & ")+C5"
Feuil2.Cells(Last + 1, "D") = "=SUM(D" & WorksheetFunction.Max(9, Cpb.Row - 2) & ":D" & Last & ")+D5"
Feuil2.Cells(Last + 1, "E") = "=SUM(E" & WorksheetFunction.Max(9, Cpb.Row - 2) & ":E" & Last & ")+E5"
With Feuil2.Range(Feuil2.Cells(Last + 1, "A"), Feuil2.Cells(Last + 1, "E"))
.Interior.ColorIndex = 45
.Font.Bold = True
End With
End If
End Sub |
Partager