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
| Sub soustotal()
Dim pb As Object
Dim C As Range
Dim i As Byte, k As Byte, PBc As Byte
Dim ReportSTe As Double, ReportSTf As Double
Dim Last As Integer
'Application.ScreenUpdating = False
j = 0
'***Partie 1 : Suppression des sous-totaux et des messages d'alerte
Set Plage = Feuil2.Range("A2:E" & Range("E" & Application.Rows.Count).End(xlUp).Row)
With Plage
Do
Set C = .Find("Total")
If Not C Is Nothing Then
Cells(C.Row, "A").EntireRow.Delete
End If
Loop While Not C Is Nothing
End With
'***Suppression des messages d'alerte spécifique à news
'With Range("C2:E" & Range("E" & Application.Rows.Count).End(xlUp).Row)
'.ClearContents
'.Interior.ColorIndex = xlNone
'End With
'*******************************************************
'Application.ScreenUpdating = True
'***Partie 3 : Définition auto de la zone d'impression
ActiveSheet.PageSetup.PrintArea = "$A$1:" & Feuil2.Range("E" & Application.Rows.Count).End(xlUp).Address
'***Partie 4 : gestion des sauts de page
PBinit = 1
Do
Call GestSautPage
'- ------------------ ERREUR ICI
Feuil2.Range("E" & Application.Rows.Count).End(xlUp).Select
'----------------------
PBc = ActiveSheet.HPageBreaks.Count
Loop While PBinit <= PBc
'***Partie 5 : Affichage du total bas de page
Application.ScreenUpdating = False
Last = Feuil2.Range("A" & Application.Rows.Count).End(xlUp).Row
Feuil2.Range(Feuil2.Cells(Last, "A"), Feuil2.Cells(Last + 1, "A")).EntireRow.Insert (xlShiftDown) '**Permet d'étendre la zone d'impression
Feuil2.Range(Feuil2.Cells(Last + 2, "A"), Feuil2.Cells(Last + 2, "E")).Copy (Feuil2.Cells(Last, "A"))
Feuil2.Cells(Last + 2, "A").EntireRow.ClearContents
Feuil2.Cells(Last + 2, "A") = "Total Général"
Feuil2.Cells(Last + 1, "A") = "Sous-Total"
If j = 0 Then
Feuil2.Cells(Last + 2, "C") = "=SUM(C2:C" & Last & ")" '+J5"
Feuil2.Cells(Last + 2, "D") = "=SUM(D2:D" & Last & ")" '+K5"
Feuil2.Cells(Last + 2, "E") = "=SUM(E2:E" & Last & ")" '+L5"
Feuil2.Cells(Last + 1, "C") = "=SUM(C2:C" & Last & ")"
Feuil2.Cells(Last + 1, "D") = "=SUM(D2:D" & Last & ")"
Feuil2.Cells(Last + 1, "E") = "=SUM(E2:E" & Last & ")"
Else
Feuil2.Cells(Last + 2, "C") = "=SUM(C" & WorksheetFunction.Max(2, Cpb.Row - 2) & ":C" & Last & ")" '+J5"
Feuil2.Cells(Last + 2, "D") = "=SUM(D" & WorksheetFunction.Max(2, Cpb.Row - 2) & ":D" & Last & ")" '+K5"
Feuil2.Cells(Last + 2, "E") = "=SUM(E" & WorksheetFunction.Max(2, Cpb.Row - 2) & ":E" & Last & ")" '+L5"
Feuil2.Cells(Last + 1, "C") = "=SUM(C" & WorksheetFunction.Max(2, Cpb.Row - 2) & ":C" & Last & ")"
Feuil2.Cells(Last + 1, "D") = "=SUM(D" & WorksheetFunction.Max(2, Cpb.Row - 2) & ":D" & Last & ")"
Feuil2.Cells(Last + 1, "E") = "=SUM(E" & WorksheetFunction.Max(2, Cpb.Row - 2) & ":E" & Last & ")"
End If
'***Suppression spécifique à news
With Feuil2.Cells(Last + 2, "E")
.Interior.ColorIndex = xlNone
End With
'********************************
With Feuil2.Range(Feuil2.Cells(Last + 1, "A"), Feuil2.Cells(Last + 1, "E"))
.Interior.ColorIndex = 40
.Font.Bold = True
End With
With Feuil2.Range(Feuil2.Cells(Last + 2, "A"), Feuil2.Cells(Last + 2, "E"))
.Interior.ColorIndex = 45
.Font.Bold = True
End With
Application.ScreenUpdating = True
End Sub |
Partager