Sub miseenforme()
'
' feuille travail
Dim DernLigne As Long, i As Long, x As Long
Application.ScreenUpdating = False
' Filtres
Sheets("Capitaux").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_capit"), CopyToRange:=Range("A8"), Unique:=0 ( Mis en jaune lors du débogage)
Sheets("Provisions").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_prov"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Immos_C").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Immos_C"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Immod_F").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Immos_F"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Stock").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Stock"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Frs").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Frs"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Clts").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Clts"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Social").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Social"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Etat").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Etat"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Autres").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Autres"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Trésorerie").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Tréso"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Régul").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Régul"), CopyToRange:=Range("A8"), Unique:=0
Sheets("Exceptionnel").Range("Balance").AdvancedFilter Action:=2, CriteriaRange:=Range("_Excep"), CopyToRange:=Range("A8"), Unique:=0
Sheets(Array("Capitaux", "Provisions", "Emprunt", "Immos_C", "Immo_F", _
"Stock", "Frs", "Clts", "Social", "Etat", "Autres", "Trésorerie", "Régul", "Exceptionnel")).Select
Sheets("Exceptionnel").Activate
Columns("A:D").Select
Columns("A:D").EntireColumn.AutoFit
Range("B1").Select
Sheets("Fourchette").Select
' Mise en forme
For i = 5 To 18
Worksheets(i).Select
DernLigne = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Range("B8:B2000").Select
Selection.Insert Shift:=xlToRight
Range("B8").Select
ActiveCell.FormulaR1C1 = "a"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"
Range("B9").Select
On Error Resume Next ' Si erreur, reprendre à l'instruction suivante
Selection.AutoFill Destination:=Range("B9:B" & DernLigne), Type:=xlFillDefault
Range("B9:B2000").Select
ActiveWindow.ScrollRow = 1
' Range("B9").Select
Range("A9").Select
If ActiveCell <> 0 Then
Range("B8").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End If
DernLigne = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Columns("A:A").ColumnWidth = 7.89
Columns("C:C").ColumnWidth = 32
Columns("D:D").ColumnWidth = 14
Columns("E:E").ColumnWidth = 14
Range("D8").Select
ActiveCell.FormulaR1C1 = "=Sommaire!R[-3]C[-2]"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=Sommaire!R[-2]C[-3]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "Variation"
Range("G8").Select
ActiveCell.FormulaR1C1 = "%"
Columns("F:F").ColumnWidth = 14
Columns("G:G").ColumnWidth = 6
Range("C8:G8").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "TrebuchetMs"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 22
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A8:G8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Range("F9").Select
ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]"
Range("F9").Select
With Selection.Font
.Name = "TrebuchetMS"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("F9").Select
Selection.AutoFill Destination:=Range("F9:F" & DernLigne), Type:=xlFillDefault
Range("G9").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[-2]=0,RC[-3]=0,RC[-3]="""",RC[-2]=""""),"""",RC[-3]/RC[-2]-1)"
Selection.Style = "Percent"
With Selection.Font
.Name = "TrebuchetMS"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("G9").Select
Selection.AutoFill Destination:=Range("G9:G" & DernLigne), Type:=xlFillDefault
Range("C9:F9").Select
Range("D:F").Select
Selection.NumberFormat = "#,##0.00"
' couleur les lignes totaux
Range("A8").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range(Selection, Selection.End(xlToRight)).Select
DernLigne = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Range(Selection, "G9:G" & DernLigne).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
With Selection.Interior
.PatternColorIndex = 8
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
ActiveSheet.Outline.ShowLevels RowLevels:=3
Range("A7").Select
' suppression plan, colonne 2 et dernière ligne
Selection.ClearOutline
Rows(DernLigne).Select
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
' copie des entetes
Sheets("entetes").Select
Range("A2:F6").Select
Selection.Copy
Worksheets(i).Select
Range("A2").Select
ActiveSheet.Paste
x = 7 + i - 5
Sheets("entetes").Select
Range("B" & x).Select
Selection.Copy
Worksheets(i).Select
Range("A2").Select
ActiveSheet.Paste
' Définition zone d'impression
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$" & DernLigne
Next i
Application.ScreenUpdating = True
End Sub