Sub Nettoyage_avant_test()
Dim Feuilles, j%
Feuilles = Array("Capitaux", "Provisions", "Emprunt", "Immos_C", "Immos_F", "Stock", "Frs", "Clts", "Social", "Etat", "Autres", "Trésorerie", "Régul", "Exceptionnel")
For j = LBound(Feuilles) To UBound(Feuilles)
Sheets(Feuilles(j)).UsedRange.Clear
Cells.ClearOutline
Next
End Sub
Sub Mise_en_forme_D()
'Déclarations des variables
Dim Feuilles, vCrits, j%, DernLigne&, dl&, i&, x&
Application.ScreenUpdating = False 'figeage rafraichissement écran
' Tableaux
Feuilles = Array("Capitaux", "Provisions", "Emprunt", "Immos_C", "Immos_F", "Stock", "Frs", "Clts", "Social", "Etat", "Autres", "Trésorerie", "Régul", "Exceptionnel")
vCrits = Array("_capit", "_prov", "_Emp", "_immoc", "_immof", "_Stock", "_Chges", "_Clt", "_Clt", "_Soc", "_Etat", "_Autres", "_Tréso", "_Régul", "_Excep")
'traitements
For j = LBound(Feuilles) To UBound(Feuilles)
Range("Balance").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Fourchette").Range(vCrits(j)), CopyToRange:=Sheets(Feuilles(j)).Range("A8:D8")
With Sheets(Feuilles(j))
.Columns("A:D").EntireColumn.AutoFit: .Columns(2).Insert Shift:=xlToRight: .Range("B8") = "a"
DernLigne = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row
.Range("B9:B" & DernLigne).Formula = "=LEFT(A9,2)"
On Error Resume Next ' Si erreur, reprendre à l'instruction suivante
If .Range("A9") <> 0 Then
.Range("B8").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5), Replace:=-1, PageBreaks:=0, SummaryBelowData:=-1
End If
dl = .Cells(Rows.Count, 2).End(3).Row
.Range("F8:G8") = Array("Variation", "%"): .Range("F9:F" & dl).Formula = "=+D9-E9"
.Range("G9:G" & dl).Formula = "=IF(OR(E9=0,D9=0,D9="""",E9=""""),"""",D9/E9-1)"
.Range("G9:G" & dl).Style = "Percent": .Range("D9:F" & dl).NumberFormat = "#,##0.00": .Outline.ShowLevels RowLevels:=2
Set r = .[A8].CurrentRegion: r.SpecialCells(12).Interior.ColorIndex = 15: .Cells.ClearOutline
r.Font.Name = "TrebuchetMs": r.Font.Size = 8: r.Borders.Value = 1: Set r = Nothing: .[A8:G8].Font.ColorIndex = 22
End With
Next
End Sub